Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Operator RSS Feeds

LIKE Operator

Posted By: Sarita Patel     Category: SQL Server     Views: 11799

This article describes the use of Like operator with different examples.

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. 

  
Share: 


Didn't find what you were looking for? Find more on LIKE Operator Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of LIKE Operator is from United States.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!