SQLite LIKE() Function

The SQLite LIKE() function is used in a WHERE clause to search for a specified pattern in a specified column.

• The LIKE(X,Y) function is used to implement the "Y LIKE X" expression.
• The optional third parameter is used to implement ESCAPE clause, for example - LIKE(X,Y,Z) function is equivalent to "Y LIKE X [ESCAPE Z]" expression.
• The NOT keyword can be used with this function to implement "NOT LIKE" expression.

The wildcards which are used in conjunction with this function are given below:

Wildcard Characters in SQLite

Syntax

The syntax for using LIKE() function in SQLite is given below:

/* Using LIKE() function */
SELECT column1, column2, ...
FROM table_name
WHERE LIKE(pattern, column);

/* Using NOT LIKE() function */
SELECT column1, column2, ...
FROM table_name
WHERE NOT LIKE(pattern, column);

The table below describes patterns which is used with LIKE() function 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 query is given below.

  SELECT * FROM Employee
  WHERE LIKE('Jo%', Name);
  

  
  

  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() function: NOT LIKE() function is used as the negation of LIKE() function. 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 NOT LIKE('Jo%', Name);
  

  
  

  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 query is mentioned below.

  SELECT * FROM Employee
  WHERE LIKE('_o%', Name);
  

  
  

  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 with NOT LIKE() function: To select all records of the Employee table with Name not containing 'o' as second character, the query is given below:.

  SELECT * FROM Employee
  WHERE NOT LIKE('_o%', Name);
  

  
  

  The result of the above 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
  6	Huang	Beijing	28	2800