Like operator compares given character string with specified
pattern. Pattern can include regular characters or wildcard characters. Below
are the possible wildcard characters to make pattern.
Wildcard character | Description | Example |
% | Compares 0 or more
characters in a string. | WHERE ProductName LIKE
'%chai%' displays all products where productname includes word 'chai'. |
_ (underscore) | Compares any single
character in a string. | WHERE ProductName LIKE '_hai'
finds all four-letter first names that end with hai. |
[ ] | Compares any single
character within the specified range or set of characters like range [a-c] or
set [abc]. | WHERE ProductName LIKE '[a-c]hai'
displays product name ending with hai and first character between a and c. |
[^] | Compares any single
character not within the specified range [^a-c] or set [^abc]. | WHERE ProductName LIKE '[^a-c]%'
displays all product name not starting with character range a,b and c. |
Note that when you perform string comparison using LIKE operator,
all character in the pattern string are significant, including leading or
trailing spaces. For example if you compare a string with pattern LIKE ‘ chai’ (note that leading
space), all rows in which the value of that column is leading space and word
chai will be returned, but rows in which the value of the column doses not
includes space i.e. ‘chai’ are not returned. But note that trailing blanks, in
the expression to which the pattern is matched, are ignored. For example if
comparison pattern is LIKE ‘chai’ and all row that start with chai and have
zero or more trailing blanks are returned.
LIKE supports ASCII pattern matching and Unicode pattern matching.
When all arguments (match_expression, pattern, and escape_character, if
present) are ASCII character data types, ASCII pattern matching is performed.
If any of the arguments are of Unicode data type, all arguments are converted
to Unicode and Unicode pattern matching is performed. When you use Unicode data
(nchar or nvarchar data types) with LIKE, trailing blanks are significant; however,
for non-Unicode data, trailing blanks are not significant.
Syntax of LIKE Operator :
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]
match_expression
is valid
expression of character string data type.
Pattern is the pattern to
search for in match_expression,
escape_character
is valid
SQL Server expression of any of the data types of the character string data
type category. escape_character has no default and must consist of
only one character.
Return type
of like operator is Boolean. LIKE returns TRUE If the match_expression matches the specified pattern.
Examples of LIKE Operator:
Example 1 : Using like operator in where
clause
SELECT ContactName, CompanyName
FROM Customers
WHERE ContactName LIKE 'paul henriot'
Output
ContactName CompanyName
Paul Henriot Vins
et alcools Chevalier
Above query compares table rows with pattern ‘paul henriot’
and returns rows having same value in contactname column.
Example 2 : Using % wildcard character in like operator
SELECT ContactName, CompanyName
FROM Customers
WHERE ContactName LIKE 'paul%'
Output
ContactName CompanyName
Paula Wilson Rattlesnake
Canyon Grocery
Paul Henriot Vins
et alcools Chevalier
Paula Parente Wellington
Importadora
LIKE ‘paul%’ pattern
returns all contactname having clumn value paul followed by zero or more
characters. You can also use NOT operator to find rows that doe not match with
pattern. For example NOT LIKE ‘paul%’.
Example 3 : Using [] square brackets wildcard charaters in
like operator
SELECT ContactName, CompanyName
FROM Customers
WHERE ContactName LIKE 'pa[lut]%'
Output
ContactName CompanyName
Patricio Simpson Cactus Comidas para llevar
Patricia McKenna Hungry
Owl All-Night Grocers
Paula Wilson
Rattlesnake Canyon Grocery
Palle Ibsen
Vaffeljernet
Paul Henriot
Vins et alcools Chevalier
Paula Parente
Wellington Importadora
As above example shows how can you use wildcard character as
literal in pattern matching. Like ‘pa[lut]%’
searches for rows having column value as first 2 characters are ‘pa’ and third
can be any of characters ‘l’,’u’,’t’ follwed
by zero or more characters.
Example 4 : Using wildcard charaters as literals in like operator
SELECT ContactName, CompanyName
FROM Customers
WHERE ContactName LIKE 'pa[%]'
Above example serches for rows having column value ‘pa%’,
here % sign is taken as charater to search from rows of table.