Chapter 07 · basic · 6 min
LIKE: pattern matching text
IN and BETWEEN match exact values or ranges. Sometimes you need to match part of a text value instead: a name starting with a letter, a code containing a substring. LIKE is SQL's pattern-matching operator, and it's what powers search boxes and autocomplete under the hood.
The dataset
The same orders table, focused on the customer column.
Schema
| id | INT |
| customer | TEXT |
| total | NUMERIC |
Example data
Two wildcards
LIKE matches text patterns using two wildcards:
| Wildcard | Matches | Example | Meaning |
|---|---|---|---|
% | any run of characters, including none | 'A%' | starts with A |
_ | exactly one character | '_a%' | second letter is 'a' |
Before you run it — of the 6 customers, whose name(s) start with 'A'?
% can go anywhere
Put % on both sides to search for a substring in any position, not just the start.
Before you run it — how many of the 6 names contain the letter 'a' anywhere (case-sensitive)?
_ pins down an exact position
_ is far more specific than %: it insists on exactly one character in that spot. '_a%' means "second letter is exactly 'a'", which is a much narrower match than "contains an a anywhere".
Before you run it — narrower than the last query. Which names have 'a' as exactly their second letter?
The performance gotcha: leading %
'%a%' is more expensive to run than 'A%'. A pattern that starts with a fixed prefix ('A%') lets the database jump straight to matching rows using a standard index, but a pattern that starts with % (like '%a%') means "the match could start anywhere", so the database is forced to scan every row and check each one. On a small practice table this is invisible; on a real table with millions of rows, a leading-wildcard search is a well-known slow query. Worth knowing before you reach for it as a default.
If you've used Excel or Google Sheets
Spreadsheets use different wildcard characters for the same idea: * for "any characters" and ? for "exactly one", instead of SQL's % and _. So Excel's COUNTIF(range, "A*") and SQL's WHERE customer LIKE 'A%' are asking the exact same question, just spelled with different symbols. Also remember: Postgres's LIKE is case-sensitive by default, unlike most spreadsheet text matching, so pair it with LOWER() on both sides whenever you want the match to ignore case.
Ready to try it for real? Carsome's listing search question below is a straightforward LIKE exercise.
That's it for filtering. Next, we sort the results and keep only the top few, with ORDER BY and LIMIT.