SQL LIKE Statement

In this article, you will learn how to use a SQL LIKE statement in SQL

Like statement in SQL

 
The SQL LIKE statement determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters.
 
During pattern matching, regular characters must exactly match the characters specified in the character string. However, wildcard characters can be matched with arbitrary fragments of the character string using wildcard characters.
 
Wildcard characters make the LIKE operator more flexible than using the = and != string comparison operators. If any one of the arguments aren't of character string data type, the SQL Server Database Engine converts it to character string data type, if it's possible in SQL
 
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column 
 
There are two wildcards often used in conjunction with the LIKE operator
  • % - The percent sign represents zero, one, or multiple characters
  • _ - The underscore represents a single character  
Syntax  
  1. SELECT column1, column2, ..    
  2. FROM table_name    
  3. WHERE columnN LIKE pattern;     

LIKE statement using the % wildcard character

 
The following example finds EmployeeName that has Column Name "Rahul" in the EmployeeDetails table
 
Syntax
  1. Select EmpName,EmpAddress,EmpCity from EmployeeDetail where EmpName LIKE 'R%'     
Example
 
 

LIKE statement using the [ ] wildcard characters

 
The following example finds employees on the EmployeeDetail table with the EmpName of "Rahul" or "Ravi"
 
Syntax 
  1. SELECT EmpName, EmpAddress, EmpCity         
  2. FROM  EmployeeDetail        
  3. WHERE EmpName LIKE '[R]avi';        
  4. GO      
Example
 
 

LIKE statement with the % wildcard character

 
The following example finds EmpName, EmpAddress,EmpCity in theEmployeeDetail table 
 
Syntax
  1. SELECT EmpName, EmpAddress, EmpCity       
  2. FROM  EmployeeDetail      
  3. WHERE EmpName LIKE 'R%'      
  4. ORDER BY EmpName    
  5. GO       
Example
 
 

NOT LIKE statement with the % wildcard character

 
The following example finds all EmpName, EmpAddress, EmpCity in theEmployeeDetail table
 
Syntax
  1. SELECT EmpName, EmpAddress, EmpCity           
  2. FROM  EmployeeDetail          
  3. WHERE EmpName NOT LIKE 'S%'          
  4. ORDER BY EmpName        
  5. GO      
Example
 
 

LIKE statement with the _ wildcard character

 
The following example finds all that have a start EmployeeID in theEmployeeDetails table. The % wildcard character is included at the end of the search pattern to match all the following characters in the EmployeeDetails column value
 
Syntax
  1. SELECT EmployeeName,EmployeeAddress,EmployeeCity      
  2. FROM EmployeeDetails      
  3. WHERE EmployeeID LIKE '1_%'      
  4. ORDER by EmployeeName;     
Example
 

Pattern matching by using LIKE statement

 
The 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 one 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 aren't significant. Unicode LIKE is compatible with the ISO standard. ASCII LIKE is compatible with earlier versions of SQL Server
 
The following is a series of examples that shows the differences in rows returned between ASCII and Unicode LIKE pattern matching
 
Syntax
  1. CREATE TABLE table1       
  2. (tName char(30));        
  3. INSERT INTO tName VALUES ('Robert King');        
  4. SELECT *         
  5. FROM table1         
  6. WHERE table1 LIKE '% King';   -- returns 1 row        
  7.         
  8. -- Unicode pattern matching with nchar column        
  9. CREATE TABLE table1 (col1 nchar(30));        
  10. INSERT INTO table1 VALUES ('Robert King');        
  11. SELECT *         
  12. FROM table1         
  13. WHERE tName LIKE '% King';   -- no rows returned        
  14.         
  15. -- Unicode pattern matching with nchar column and RTRIM        
  16. CREATE TABLE table1 (tName nchar (30));        
  17. INSERT INTO table1 VALUES ('Robert King');        
  18. SELECT *         
  19. FROM table1         
  20. WHERE RTRIM(tName) LIKE '% King';  

Summary

 
In this tutorial, we learned the basics of the SQL LIKE  statement.