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
Symbol | Description | Example |
---|---|---|
% | Represents zero, one or multiple characters. | 'J%' represents a value that start with "J", for example - John, Jo and Jack etc. |
_ | Represents one character. | '_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc. |
ESCAPE | Allow test for literal instances of a wildcard character such as % or _ | 'J%!%' ESCAPE '!' represents a value that starts with J and ends in %, for example - John%, Jo% and Journey% etc. |
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 (_).
Pattern | Description |
---|---|
'J%' | A value that start with "J". |
'%n' | A value that end with "n". |
'%oh%' | A value that have "oh" in any position. |
'_o%' | A value that have "o" in the second position. |
'J_%' | A value that start with "J" and have at least 2 characters. |
'J__%' | A value that start with "J" and have at least 3 characters. |
'J%n' | A value that start with "J" and ends with "n". |
Example:
Consider a database containing a table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
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 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
❮ SQLite Functions