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.
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' |
Result of 'A%' against our customers:
| customer |
|---|
| Aisyah |
Names starting with A
% can go anywhere
Put % on both sides to search for a substring in any position, not just the start:
| customer |
|---|
| Aisyah |
| Bala |
| Farid |
| Gina |
Names containing the letter 'a', anywhere
_ 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":
| customer |
|---|
| Bala |
| Farid |
Second letter is exactly 'a'
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.