LIKE and text patterns

Game: chapter III

When you know part of a value but not all of it, = is too strict. LIKE matches a pattern.

Two wildcards

column LIKE 'pattern' is true when the text in the column fits the pattern. A pattern is ordinary text with two special characters. The percent sign, %, stands for any run of characters, including none at all. The underscore, _, stands for exactly one character. Everything else in the pattern must match as written.

So 'Choc%' means "starts with Choc", '%loaf' means "ends with loaf", and '%croissant%' means "contains croissant anywhere". The examples use the bakery database from the bakery database page.

SELECT name, price
FROM product
WHERE name ILIKE '%croissant%'
ORDER BY id;

Both the plain croissant and the almond croissant match, even though the word sits at the start of one name and at the end of the other, and even though one is written with a capital letter: that is what the I of ILIKE is for. The next section explains.

Case: LIKE and ILIKE

The game's terminal follows PostgreSQL: LIKE is case-sensitive, like =, so '%croissant%' finds "Almond croissant" but not "Croissant", and ILIKE is the same pattern match with case ignored. Use LIKE when you know how the text is written, ILIKE when you do not. In plain SQLite, which has no ILIKE, write lower(name) LIKE '%croissant%' instead.

SELECT name, price
FROM product
WHERE name LIKE '%croissant%'
ORDER BY id;

Grammar: LIKE and ILIKE

Patterns are written with LIKE, or ILIKE to ignore case, and nothing else: GLOB and REGEXP are not standard SQL, and neither is COLLATE NOCASE. A query with a red edge is one the game's terminal refuses: the answer under it is the terminal's own.

SELECT name
FROM product
WHERE name GLOB 'C*';

SELECT name
FROM product
WHERE name LIKE 'C%'
ORDER BY id;

The others, with what to write instead:

The underscore: a fixed length

The underscore is handy for codes and dates whose shape you know. The pattern '2026-03-0_' matches any of the first nine days of March 2026 and nothing else, because exactly one character must follow the zero. Two underscores require two characters, and so on.

SELECT name, joined
FROM customer
WHERE joined LIKE '2024-0_-0_'
ORDER BY id;

That keeps the customers who joined in the first nine days of any month from January to September 2024. Every value in the column has the same length, so the pattern reads like a template.

Combining a pattern with other conditions

LIKE is a condition like any other, so it combines with AND, OR and NOT. NOT LIKE keeps the rows that do not match. A common pattern is to search broadly, then narrow down with a second condition. The bakery's emails are typed as the customers wrote them, some with capitals: ILIKE finds every email at mail.com whatever its case, and NOT LIKE then takes away those written in lower case.

SELECT name, email
FROM customer
WHERE email ILIKE '%@mail.com'
  AND email NOT LIKE '%@mail.com'
ORDER BY id;

Read it step by step: emails at mail.com in any case, minus the ones in lower case, leaves the two typed with capitals. A pattern that ends without a % is also defeated by a stray space: Emma Novak's email ends with one, so email LIKE '%post.net' misses it, while trim(email) LIKE '%post.net', which removes the spaces at both ends first, finds it.

Searching more than one word

When you are not sure of the spelling, try several patterns joined with OR, each one short: a stem such as ILIKE '%choc%' catches both "Chocolate eclair" and "Pain au chocolat" at once. Shorter patterns find more rows, longer ones fewer. Run a broad pattern, read what comes back, then tighten it.

SELECT name
FROM product
WHERE name ILIKE '%choc%' OR name ILIKE '%tart%'
ORDER BY id;

A pattern search answers the question "which rows mention this?", but a mention is not proof. A row that contains a word may be about something else entirely, so read the matching rows rather than counting them blindly.

Performance, briefly

A pattern that starts with % forces the database to read every row, because an ordinary index cannot help it find text that may begin anywhere. On a small table that does not matter. On millions of rows it does, and a pattern that begins with fixed text, such as 'Choc%', can use an index and is much cheaper.

Common mistakes

Using = with a wildcard: name = '%tart%' looks for a name that is literally percent-t-a-r-t-percent, and finds nothing. Forgetting the leading %, so that 'croissant%' misses "Almond croissant". Using the * wildcard of file names or of GLOB inside LIKE. Assuming that LIKE ignores case: in PostgreSQL and in the game it does not, and ILIKE does. Trusting a pattern that is too short: '%a%' matches almost everything.

Try it: Every croissant and Untidy emails, on the exercises page.

In the game: Chapter III, A Plate in the Dark, practises LIKE.