LIKE and pattern matching

= answers “is this text exactly that text?”. Real questions are often looser: names that start with “Sri”, product codes that end in “500G”, email addresses on a particular domain. LIKE compares a value with a pattern, a string in which two characters stand for “something here”.

The two wildcards

In a LIKE pattern:

• % stands for any run of characters, including none at all;
• _ stands for exactly one character;
• every other character stands for itself.

The pattern has to describe the whole value, not just a part of it. 'Sri%' therefore means “starts with Sri”, '%Rao' means “ends with Rao”, '%sri%' means “contains sri somewhere”, and five underscores mean “exactly five characters long”.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO customers VALUES
  (1, 'Srinivas Rao'), (2, 'Sridevi Nair'), (3, 'Asrith Varma'),
  (4, 'Sunil Sri'),    (5, 'Srini'),        (6, 'Priya Iyer');

-- Each column is one pattern: 1 means the name matches it, 0 that it does not.
SELECT name,
       name LIKE 'Sri%'  AS starts_sri,
       name LIKE '%sri%' AS has_sri,
       name LIKE '%Rao'  AS ends_rao,
       name LIKE '_____' AS five_chars
FROM customers;

-- The same patterns filter rows in WHERE.
SELECT id, name
FROM customers
WHERE name LIKE 'Sri%';

┌──────────────┬────────────┬─────────┬──────────┬────────────┐
│     name     │ starts_sri │ has_sri │ ends_rao │ five_chars │
├──────────────┼────────────┼─────────┼──────────┼────────────┤
│ Srinivas Rao │ 1          │ 1       │ 1        │ 0          │
│ Sridevi Nair │ 1          │ 1       │ 0        │ 0          │
│ Asrith Varma │ 0          │ 1       │ 0        │ 0          │
│ Sunil Sri    │ 0          │ 1       │ 0        │ 0          │
│ Srini        │ 1          │ 1       │ 0        │ 1          │
│ Priya Iyer   │ 0          │ 0       │ 0        │ 0          │
└──────────────┴────────────┴─────────┴──────────┴────────────┘
┌────┬──────────────┐
│ id │     name     │
├────┼──────────────┤
│ 1  │ Srinivas Rao │
│ 2  │ Sridevi Nair │
│ 5  │ Srini        │
└────┴──────────────┘

The first table puts each pattern in a column of its own, so you can see which names it matches (1) and which it does not (0). Two results deserve a second look. “Asrith Varma” contains sri in the middle of a word, and '%sri%' does not care about word boundaries. And '%sri%' matched names that start with a capital S: in SQLite, LIKE ignores the difference between capital and small letters, which is the subject of the next section. The second query uses a pattern the usual way, as a condition in WHERE.

A pattern can also be tried on a single value, without any table, which is a quick way to test one before you use it:

-- Each _ stands for exactly one character; % for any number of them, even none.
SELECT 'Sri' LIKE 'S_i'  AS one,
       'Sri' LIKE 'S__i' AS two,
       'Sri' LIKE 'S%i'  AS pct;

┌─────┬─────┬─────┐
│ one │ two │ pct │
├─────┼─────┼─────┤
│ 1   │ 0   │ 1   │
└─────┴─────┴─────┘

'S__i' asks for four characters, an S, any two and an i, so it does not match the three letters of Sri. % may stand for nothing at all, so 'S%i' matches.

Letter case: the engines disagree again

How LIKE treats capital and small letters is one of the differences you meet first when moving a query between engines:

• SQLite ignores letter case in LIKE, but only for the 26 letters of the English alphabet. Accented and other non-English letters must match exactly.
• PostgreSQL makes LIKE case-sensitive with its default collations. Its own operator ILIKE ignores case, using the case rules of the active locale. ILIKE is a PostgreSQL extension, not part of the SQL standard.
• MySQL compares with the collation of the column, and its default collation is case-insensitive, so LIKE ignores case there unless the column or the pattern uses a case-sensitive or binary collation.

-- SQLite's LIKE ignores the case of the 26 ASCII letters only.
SELECT 'ECLAIR' LIKE 'eclair' AS ascii_letters,
       'ÉCLAIR' LIKE 'éclair' AS accented_letters,
       lower('ÉCLAIR')        AS lower_result;

-- GLOB never ignores case.
SELECT 'Pune' GLOB 'P*' AS capital_p,
       'Pune' GLOB 'p*' AS small_p;

┌───────────────┬──────────────────┬──────────────┐
│ ascii_letters │ accented_letters │ lower_result │
├───────────────┼──────────────────┼──────────────┤
│ 1             │ 0                │ Éclair       │
└───────────────┴──────────────────┴──────────────┘
┌───────────┬─────────┐
│ capital_p │ small_p │
├───────────┼─────────┤
│ 1         │ 0       │
└───────────┴─────────┘

'ÉCLAIR' LIKE 'éclair' is false in SQLite because É is outside the ASCII range, and lower() has the same limit: it turned ÉCLAIR into Éclair, lowering only the English letters. GLOB, SQLite’s other pattern operator, never ignores case.

For a pattern that should ignore case in every engine, lower both sides yourself: lower(email) LIKE '%.in'. That works in PostgreSQL, MySQL and SQLite alike, within SQLite’s ASCII limit.

Searching for a real % or _

Product codes, file names and discount labels often contain _ or % themselves. Because those are wildcards, a pattern meant to find them can match far too much. This is the most common LIKE bug:

CREATE TABLE products (code TEXT PRIMARY KEY, label TEXT NOT NULL);
INSERT INTO products VALUES
  ('TEA_500G',  'Assam tea, 50% off'),
  ('TEAX500G',  'Tea sampler'),
  ('TEA-1KG',   'Nilgiri tea, 5% cashback'),
  ('COFFEE_1K', 'Filter coffee');

-- _ means "any one character", so TEA_% also matches TEAX and TEA-.
-- With ESCAPE '!', the pattern TEA!_% looks for a real underscore.
SELECT code,
       code LIKE 'TEA_%'               AS any_character,
       code LIKE 'TEA!_%' ESCAPE '!'   AS real_underscore
FROM products;

-- Labels that contain a real percent sign.
SELECT label
FROM products
WHERE label LIKE '%!%%' ESCAPE '!';

┌───────────┬───────────────┬─────────────────┐
│   code    │ any_character │ real_underscore │
├───────────┼───────────────┼─────────────────┤
│ COFFEE_1K │ 0             │ 0               │
│ TEA-1KG   │ 1             │ 0               │
│ TEAX500G  │ 1             │ 0               │
│ TEA_500G  │ 1             │ 1               │
└───────────┴───────────────┴─────────────────┘
┌──────────────────────────┐
│          label           │
├──────────────────────────┤
│ Assam tea, 50% off       │
│ Nilgiri tea, 5% cashback │
└──────────────────────────┘

'TEA_%' was meant to find codes that start with TEA_, but its _ matched the X of TEAX500G and the - of TEA-1KG as well. The fix is an escape character: the clause ESCAPE '!' says that in this pattern !_ means a real underscore and !% a real percent sign (and !! a real exclamation mark). The second query uses '%!%%': anything, then a real %, then anything.

The engines do not agree on what happens when you leave out ESCAPE:

• PostgreSQL and MySQL treat a backslash as the escape character by default, so 'TEA\_%' works there.
• SQLite has no default escape character, which is also what the SQL standard prescribes. Without an ESCAPE clause a backslash is an ordinary character, and 'TEA\_%' would look for a real backslash.
• MySQL also treats a backslash inside any string literal as the start of an escape sequence, so the one-character string '\' has to be written '\\' there.

Writing the clause yourself with a character such as !, which needs no special treatment anywhere, gives the same result in all three engines.

GLOB in SQLite

SQLite has a second operator, GLOB, with the wildcards of file names on Unix systems: * for any run of characters, ? for exactly one, and square brackets for one character from a set, such as [0-9] for a digit or [A-Z] for a capital letter. GLOB always respects letter case.

CREATE TABLE skus (code TEXT PRIMARY KEY);
INSERT INTO skus VALUES ('SKU-042'), ('SKU-42'), ('sku-042'), ('SKU-0A2'), ('SKU-1234');

-- GLOB: * any run of characters, ? exactly one, [0-9] one digit. Case counts.
SELECT code,
       code GLOB 'SKU-[0-9][0-9][0-9]' AS three_digits,
       code GLOB 'SKU-*'               AS any_suffix,
       code GLOB '???-???'             AS seven_chars
FROM skus;

┌──────────┬──────────────┬────────────┬─────────────┐
│   code   │ three_digits │ any_suffix │ seven_chars │
├──────────┼──────────────┼────────────┼─────────────┤
│ SKU-042  │ 1            │ 1          │ 1           │
│ SKU-42   │ 0            │ 1          │ 0           │
│ sku-042  │ 0            │ 0          │ 1           │
│ SKU-0A2  │ 0            │ 1          │ 1           │
│ SKU-1234 │ 0            │ 1          │ 0           │
└──────────┴──────────────┴────────────┴─────────────┘

Only SKU-042 has exactly three digits after the dash. sku-042 fails every pattern that spells out SKU because GLOB respects case, while ???-??? is about length and position only. LIKE cannot say “a digit” at all, which is why GLOB is handy for checking the shape of codes. GLOB is SQLite’s own operator; in PostgreSQL and MySQL the same checks are written as regular expressions.

Missing values and invisible spaces

Two edge cases surprise people who clean data with LIKE:

CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO customers VALUES
  (1, 'asha@mail.in'),
  (2, 'farhan@mail.com'),
  (3, NULL),             -- no email given
  (4, 'joseph@mail.in ') -- typed with a space at the end
;

SELECT count(*) AS all_rows FROM customers;
SELECT count(*) AS ends_in FROM customers WHERE email LIKE '%.in';
SELECT count(*) AS not_ends_in FROM customers WHERE email NOT LIKE '%.in';
SELECT count(*) AS ends_in_trimmed FROM customers WHERE trim(email) LIKE '%.in';

┌──────────┐
│ all_rows │
├──────────┤
│ 4        │
└──────────┘
┌─────────┐
│ ends_in │
├─────────┤
│ 1       │
└─────────┘
┌─────────────┐
│ not_ends_in │
├─────────────┤
│ 2           │
└─────────────┘
┌─────────────────┐
│ ends_in_trimmed │
├─────────────────┤
│ 2               │
└─────────────────┘

There are four customers, but the LIKE and NOT LIKE counts add up to three. The customer without an email (NULL) is in neither: when the value is missing, the database cannot say whether it matches, so both conditions are unknown and WHERE drops the row. The next lesson is about exactly this. The other surprise is Joseph, whose address was saved with a space at the end: the pattern has to match the whole value, space included. trim() removes spaces at both ends before the comparison.

When another tool is the right one

Patterns that LIKE cannot express, such as “two to four digits, then a capital letter”, need regular expressions. They are written differently in each engine, and SQLite has none of its own:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO customers VALUES (1, 'Srinivas Rao'), (2, 'Priya Iyer');

-- PostgreSQL's case-insensitive ILIKE is not SQLite (or MySQL) syntax.
SELECT name FROM customers WHERE name ILIKE 'sri%';

-- SQLite knows the REGEXP operator, but only a program that adds a regexp()
-- function can run it (the sqlite3 shell does; the browser runner does not).
SELECT name FROM customers WHERE name REGEXP '^Sri';

-- Plain LIKE runs everywhere.
SELECT name FROM customers WHERE name LIKE 'sri%';

┌──────────────┐
│     name     │
├──────────────┤
│ Srinivas Rao │
└──────────────┘

Parse error near line 5: near "ILIKE": syntax error
Parse error near line 9: no such function: REGEXP

SQLite rejects ILIKE with a syntax error. It accepts the REGEXP operator but has no regexp() function to run it unless the program using SQLite adds one. The sqlite3 command-line program adds one, so the REGEXP query works there; the browser runner here does not. The script carries on after each error, so the plain LIKE at the end still prints its row. Regular expressions get a lesson of their own in the text-search module.

Pattern matching at a glance

• SQLite 3.49: LIKE ignores the case of the 26 English letters only; there is no escape character unless you declare one with ESCAPE; GLOB adds character classes such as [0-9]; regular expressions need a regexp() function that the application provides (the sqlite3 shell has one).
• PostgreSQL 18: LIKE respects letter case and ILIKE ignores it; the backslash is the default escape character; SIMILAR TO and the regular-expression operators ~ and ~* cover character classes.
• MySQL 8.4: LIKE follows the collation, which ignores case by default; the backslash is the default escape character unless the SQL mode NO_BACKSLASH_ESCAPES is on; regular expressions use REGEXP, also spelled RLIKE.

Patterns and indexes

An index keeps a column’s values sorted, like the names in a phone book. A pattern with a fixed start, such as 'Sri%', tells the database where in that order to start reading. A pattern that starts with a wildcard, such as '%vas', could match anywhere, so the database has to read every row. SQLite can show its plan:

-- An index sorted without regard to case, which SQLite's LIKE can use.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE INDEX customers_by_name ON customers (name COLLATE NOCASE);

-- The last column, detail, says how SQLite will find the rows.
EXPLAIN QUERY PLAN SELECT id FROM customers WHERE name LIKE 'Sri%';
EXPLAIN QUERY PLAN SELECT id FROM customers WHERE name LIKE '%vas';

┌────┬────────┬─────────┬─────────────────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                                   detail                                    │
├────┼────────┼─────────┼─────────────────────────────────────────────────────────────────────────────┤
│ 2  │ 0      │ 156     │ SEARCH customers USING COVERING INDEX customers_by_name (name>? AND name<?) │
└────┴────────┴─────────┴─────────────────────────────────────────────────────────────────────────────┘
┌────┬────────┬─────────┬────────────────┐
│ id │ parent │ notused │     detail     │
├────┼────────┼─────────┼────────────────┤
│ 2  │ 0      │ 216     │ SCAN customers │
└────┴────────┴─────────┴────────────────┘

SEARCH … USING COVERING INDEX means SQLite would read only the part of the index between two values; SCAN customers means it would read the whole table. SQLite uses an index for LIKE only in particular cases: here the index is sorted without regard to case (COLLATE NOCASE), to match the case-insensitive LIKE, and the pattern does not start with a wildcard. PostgreSQL has a similar rule for its B-tree indexes: one can serve name LIKE 'Sri%' but not name LIKE '%vas', and in a database that does not use the C locale the index needs a special operator class for it. The performance module shows how to read these plans properly.

Key takeaways

• % matches any run of characters and _ exactly one; a LIKE pattern has to match the whole value.
• Letter case: SQLite’s LIKE ignores it for English letters only, PostgreSQL’s respects it (use ILIKE), MySQL’s follows the collation.
• To find a real % or _, write an escape character with ESCAPE '!'; it behaves the same in all three engines.
• NULL matches neither LIKE nor NOT LIKE, and a stray space at the end is part of the value.
• SQLite’s GLOB adds character classes such as [0-9]; regular expressions differ by engine.