SQL - LIKE Operator

The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a specified column. The wildcards which are used in conjunction with the LIKE operator are given below:

Note: The NOT LIKE operator is the negation of LIKE operator.

Wildcard Characters in MySQL

Wildcard Characters in SQL Server

Wildcard Characters in Oracle

Wildcard Characters in MS Access

Syntax

The syntax for using LIKE operator is given below:

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

The table below describes patterns which is used with LIKE operator and uses (%) and (_).

Example:

Consider a database containing a table called Employee with the following records:

• Using the % Wildcard : To select all records of the Employee table with Name starting with 'Jo', the SQL code is given below.

  SELECT * FROM Employee
  WHERE Name LIKE 'Jo%';
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  3	Jo	Paris	27	2800

• Using the % Wildcard with NOT LIKE operator: NOT LIKE operator is used as the negation of LIKE operator. For example, to select all records of the Employee table with Name not starting with 'Jo', the following query can be used:

  SELECT * FROM Employee
  WHERE Name NOT LIKE 'Jo%';
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  2	Marry	New York	24	2750
  4	Kim	Amsterdam	30	3100
  5	Ramesh	New Delhi	28	3000
  6	Huang	Beijing	28	2800

• Using the _ Wildcard : To select all records of the Employee table with Name containing 'o' as second character, the SQL code is mentioned below.

  SELECT * FROM Employee
  WHERE Name LIKE '_o%';
  

  
  

  The result of the above code will be:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  3	Jo	Paris	27	2800

• Using the [] Wildcard : To select all records of the Employee table with Name starting with 'K', 'R' or 'M', the SQL code is:

  SELECT * FROM Employee
  WHERE Name LIKE '[KRM]%';
  

  
  

  The result of the code will be:

  EmpID	Name	City	Age	Salary
  2	Marry	New York	24	2750
  4	Kim	Amsterdam	30	3100
  5	Ramesh	New Delhi	28	3000

• Using the ^ Wildcard : To select all records of the Employee table with Name not starting with 'K', 'R' or 'M', the SQL code is:

  SELECT * FROM Employee
  WHERE Name LIKE '[^KRM]%';
  

  
  

  The result of the code will be:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  3	Jo	Paris	27	2800
  6	Huang	Beijing	28	2800

• Using the - Wildcard : To select all records of the Employee table with Name starting with 'G' to 'J', the SQL code is:

  SELECT * FROM Employee
  WHERE Name LIKE '[G-J]%';
  

  
  

  The result produced by the code will be following:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  3	Jo	Paris	27	2800
  6	Huang	Beijing	28	2800