Post
Ask Question

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  
Syntax  
  1. SELECT column1, column2, ..    
  2. FROM table_name    
  3. WHERE columnN LIKE pattern;     
The above syntax will SELECT column1,column2 in table_name where columnN LIKE pattern statement.
 

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
 
 
The above query selects EmpName, EmpAddress, EmpCity from the EmployeeDetail table name where EmpName starts with 'R'. 
 

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
 
The above query select data from EmpName,EmpAddress,EmpCity in EmployeeDetail table name where EmpName is '[R]avi';
 

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
 
 
The above query select  is EmpName, EmpAddress and EmpCity columns from EmployeeDetail table name where EmpName starts with 'R' and arranged according to EmpName column.
 

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
 
 
The above query column name is EmployeeName,EmployeeAddress,EmployeeCity in EmployeeDetail table name where EmployeeID is of the form '1_%' (% means any value) and arranged according to EmployeeName column. 
 

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';  
The above query creates a table1 column name with column name tName and inserts value('Robert King') into 'tName' and selects all record in Table1, WHERE RTRIM(tName) is of the form '% King';  
 

Summary

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


Similar Articles