SQL LIKE Statement

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  
  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
  1. Select EmpName,EmpAddress,EmpCity from EmployeeDetail where EmpName LIKE 'R%'     

LIKE statement using the [ ] wildcard characters

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

LIKE statement with the % wildcard character

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

NOT LIKE statement with the % wildcard character

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

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
  1. SELECT EmployeeName,EmployeeAddress,EmployeeCity      
  2. FROM EmployeeDetails      
  3. WHERE EmployeeID LIKE '1_%'      
  4. ORDER by EmployeeName;     

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
  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        
  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        
  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';  


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