SQL / SELECTING ROWS
Pattern matching with LIKE and wildcards
Filter rows by text pattern with LIKE, place % and _ where the value is unknown, and match literal wildcards using ESCAPE.
What you will learn
- Write % for zero or more characters and _ for exactly one character
- Anchor a search: 'AB%' tests the start, '%AB' the end, '%AB%' anywhere
- Match a literal % or _ with an escape character: LIKE '%!%%' ESCAPE '!'
- Know that NULL values match neither LIKE nor NOT LIKE
Understanding Pattern matching with LIKE and wildcards
LIKE compares a value against a pattern rather than a fixed literal, and it is true only when the pattern accounts for the entire value, first character to last. Two characters in the pattern are special: % stands for any run of characters including an empty run, and _ stands for exactly one character. Everything else must appear literally and in order. The useful mental model is a template laid over the whole string, where % and _ mark the only places it is allowed to stretch.
That whole-value rule explains nearly every surprise. `name LIKE 'Steel'` returns exactly the rows that `name = 'Steel'` returns, because a pattern with no wildcard has no slack, so 'Steel bracket' is not a match. Adding percent signs adds slack where you want it: '%Steel%' is the substring search people usually mean, 'Steel%' pins the beginning and frees the end. And because % also matches zero characters, 'AB-1%' matches the code 'AB-1' itself, while 'AB-1_' would insist on one more character.
Two behaviours are worth committing to memory before you trust a LIKE filter. A NULL value yields NULL rather than false, so NULL rows are discarded by LIKE and by NOT LIKE alike, and you have to ask for them separately with IS NULL. Case handling is not standardised either: PostgreSQL's LIKE is case sensitive and offers ILIKE alongside it, MySQL follows the column's collation and is usually case insensitive, and SQLite folds only ASCII letters. Write patterns that do not depend on the default, or normalise both sides with lower().
CREATE TABLE product (
code TEXT,
name TEXT
);
INSERT INTO product (code, name) VALUES
('AB-1001', 'Steel bracket'),
('AB-1002', 'Steel bolt'),
('AB-10', 'Steel washer'),
('CD-2001', 'Brass bracket'),
('ZAB-1003', 'Steel nut');
SELECT code, name
FROM product
WHERE code LIKE 'AB-1%'
ORDER BY code;LIKE matches the pattern against the whole value, so any character you do not spell out must be covered by a wildcard.
Worked examples
Underscore counts characters
Shows that each _ consumes exactly one character, so the pattern also fixes the length of the value.
CREATE TABLE part (code TEXT);
INSERT INTO part (code) VALUES ('AB-10'), ('AB-1001'), ('AB-1002'), ('AB-100X');
SELECT code
FROM part
WHERE code LIKE 'AB-____'
ORDER BY code;Example explained
Line 1'AB-' must appear literally at the very start of the value.
Line 2The four underscores each match one character of any kind, so 'AB-100X' qualifies even though X is not a digit.
Line 3'AB-10' is rejected on length, not content: only two characters follow the dash.
Line 4Use 'AB-1___%' style patterns when you want a minimum length instead of an exact one.
Searching for a literal percent sign
Uses ESCAPE to look for the character % inside the text instead of treating it as a wildcard.
CREATE TABLE promo (label TEXT);
INSERT INTO promo (label) VALUES ('50% off'), ('Half price'), ('5 percent off'), ('Buy 1 get 1');
SELECT label
FROM promo
WHERE label LIKE '%!%%' ESCAPE '!';Example explained
Line 1ESCAPE '!' declares ! as the escape character for this pattern only.
Line 2The middle '!%' is therefore a literal percent sign, while the first and last % stay wildcards.
Line 3So the pattern reads: anything, then a percent sign, then anything.
Line 4Without ESCAPE the pattern '%%%' is three wildcards and matches every non-NULL label.
NOT LIKE and NULL
Demonstrates that rows with NULL disappear from a NOT LIKE filter as well as from a LIKE filter.
CREATE TABLE contact (id INTEGER, email TEXT);
INSERT INTO contact (id, email) VALUES
(1, 'ana@example.com'),
(2, 'bo@test.org'),
(3, NULL);
SELECT id, email
FROM contact
WHERE email NOT LIKE '%@example.com';Example explained
Line 1The pattern ends with literal text, so the value may begin with anything but must end with @example.com.
Line 2Row 1 matches the pattern, so NOT LIKE is false and the row is filtered out.
Line 3Row 3 is NULL: NULL LIKE anything is NULL, and NOT NULL is still NULL, which WHERE treats as not true.
Line 4To keep it, write WHERE email IS NULL OR email NOT LIKE '%@example.com'.
Important notes
A pattern that begins with % cannot use an ordinary B-tree index on the column, so '%son' scans the whole table while 'son%' can seek; keep the wildcard on the right when you have the choice.
ESCAPE applies only to the pattern it is written on, and inside that pattern the escape character must be doubled ('!!') to mean itself, so pick a character that does not appear in your search text.
Common mistakes
Writing WHERE name LIKE 'bracket' and expecting a substring search: with no wildcard, LIKE behaves like =, so 'Steel bracket' is never returned and the query looks like it found no data.
Using _ where % was meant, as in LIKE 'AB-_': that demands exactly one character after the dash, so 'AB-1001' is silently excluded and you conclude the rows are missing.
Searching for a discount label with LIKE '%50%%' and no ESCAPE: the trailing %% are two wildcards, so the query matches '150 units' and every other value containing 50, not just '50% off'.
Try it yourself
Change, predict, then run
Create a table with the city names 'San Jose', 'San Antonio', 'Nashville', 'Paris', 'Perth' and 'Jacksonville', then write three queries: one returning names that start with 'San ', one returning names containing 'ville', and one returning only the five-letter names using nothing but underscores.
Open the SQL workspaceCheck your understanding
A code column holds 'AB-1', 'AB-12' and 'ZAB-1'. Which rows does WHERE code LIKE 'AB-1%' return?
- 'AB-1' and 'AB-12', because % also matches zero characters but the value must begin with AB-1
- Only 'AB-12', because % has to stand for at least one character
- All three, because LIKE looks for the pattern anywhere inside the value
- Only 'AB-1', because % matches at most one character
Show answer
% matches a run of zero or more characters, so 'AB-1' satisfies 'AB-1%' with nothing left over, and 'AB-12' satisfies it with '2'. Option 3 is the tempting one because search boxes in other tools do substring matching, but a LIKE pattern is matched against the entire value starting at the first character, and there is no wildcard before 'AB', so the leading Z in 'ZAB-1' has nothing to match it.