SQL LIKE: search for a pattern in text

The equality operator = only finds identical text. To find the names that start with a letter, the emails that contain a domain or the products that end with a word, you use LIKE with wildcard characters.

Syntax

WHERE column LIKE 'pattern'

How it works

A LIKE pattern can contain two wildcards:

The most common patterns: 'abc%' (starts with), '%abc' (ends with) and '%abc%' (contains).

In SQLite, LIKE ignores case for unaccented letters: 'b%' also finds “Bernard”. This behaviour depends on the database: PostgreSQL is case-sensitive (it offers ILIKE), MySQL depends on the collation.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT prenom, nom
FROM clients
WHERE nom LIKE 'B%'
LIMIT 8;

SELECT nom, categorie
FROM produits
WHERE nom LIKE '%de%';

SELECT nom
FROM produits
WHERE nom LIKE '%e';

SELECT DISTINCT prenom
FROM clients
WHERE prenom LIKE '___a';

Common mistakes

SELECT * FROM clients WHERE nom LIKE 'B';

SELECT * FROM clients WHERE nom LIKE 'B%';

SELECT * FROM clients WHERE nom = 'B%';

SELECT * FROM clients WHERE nom LIKE 'B%';

SELECT * FROM clients WHERE prenom LIKE 'Lea';

SELECT * FROM clients WHERE prenom LIKE 'Léa';

Exercises

Try writing the query yourself before opening the solution.

SELECT prenom, nom, email FROM clients WHERE email LIKE '%martin%';

SELECT nom, prix FROM produits WHERE nom LIKE 'C%';

Frequently asked questions