SQLite - LIKE Clause

SQLite - The LIKE Operator

The SQLite LIKE operator is used in a WHERE clause to search for a specified pattern in a string column. It’s a powerful tool for flexible text matching.

Two wildcards are used with LIKE:

• The percent sign (%): Represents zero, one, or multiple characters.
• The underscore (_): Represents a single character.

Syntax

The LIKE operator is used within a WHERE clause as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern';

Here are common pattern examples:

Pattern	Description
'A%'	Finds any values that start with ‘A’.
'%a'	Finds any values that end with ‘a’.
'%lux%'	Finds any values that contain ‘lux’ at any position.
'_r%'	Finds any values that have ‘r’ in the second position.
'A_%'	Finds any values that start with ‘A’ and are at least two characters long.
'A__%	Finds any values that start with ‘A’ and are at least three characters long.
'B%t'	Finds any values that start with ‘B’ and end with ‘t’.

Example

Let’s consider a CUSTOMERS table with the following records:

ID          NAME            EMAIL
----------  --------------  ----------------------
1           Paul Smith      paul.s@example.com
2           Allen Jones     allen.j@work.net
3           Teddy Becker    teddy.b@example.com
4           Mark Wright     mark.w@example.org
5           David Austin    dave.a@work.net

This query finds all customers whose email is from the example.com domain:

sqlite> SELECT * FROM CUSTOMERS WHERE EMAIL LIKE '%@example.com';

ID          NAME            EMAIL
----------  --------------  ----------------------
1           Paul Smith      paul.s@example.com
3           Teddy Becker    teddy.b@example.com
4           Mark Wright     mark.w@example.org

This query finds all customers whose name starts with ‘A’ and has at least 5 characters:

sqlite> SELECT * FROM CUSTOMERS WHERE NAME LIKE 'A____%';

ID          NAME            EMAIL
----------  --------------  ----------------------
2           Allen Jones     allen.j@work.net

Escaping Wildcard Characters

What if you need to search for a literal % or _? You can use the ESCAPE clause to specify an escape character.

-- Suppose we have a table of discounts
-- NAME         VALUE
-- -----------  -----
-- 'Sale_50'    '50%'
-- 'Sale_75'    '75%'

-- Find discounts that are exactly '50%'
SELECT * FROM DISCOUNTS WHERE VALUE LIKE '50\%' ESCAPE '\';

Bonus: The GLOB Operator

SQLite also provides a non-standard GLOB operator which uses Unix-style wildcards. It is case-sensitive by default.

• Asterisk (*): Equivalent to LIKE’s %.
• Question Mark (?): Equivalent to LIKE’s _.

-- Find emails ending in .net using GLOB (case-sensitive)
sqlite> SELECT * FROM CUSTOMERS WHERE EMAIL GLOB '*.net';

ID          NAME            EMAIL
----------  --------------  ------------------
2           Allen Jones     allen.j@work.net
5           David Austin    dave.a@work.net