= needs the whole text to match. Often you know only part of it: the products whose name ends with "Plush", the emails at one domain.
Patterns
LIKE compares text with a pattern. Two characters in a pattern have a special meaning:
| Character | Matches | Example | Matches |
|---|---|---|---|
% | any number of characters, also none | 'Kraken%' | 'Kraken Plush', 'Kraken' |
_ | exactly one character | '_o%' | 'Noah Kim', 'Sofia Rossi' |
Every other character must match itself.
sql
SELECT name
FROM products
WHERE name LIKE '%Mug';products
| id | name |
|---|---|
| 8 | Docker Sticker Pack |
| 9 | Holo Kraken Sticker |
| 10 | Kernel Panic Mug |
| 11 | sudo Coffee Mug |
| 12 | Mystery Box |
| Pattern | Means |
|---|---|
'Mug%' | starts with Mug |
'%Mug' | ends with Mug |
'%Mug%' | has Mug anywhere |
'___' | exactly three characters |
NOT LIKE keeps the rows that do not match.
Case
LIKE is case-sensitive in PostgreSQL. '%mug%' does not match 'Kernel Panic Mug'.
ILIKE is a LIKE that ignores case:
sql
SELECT name
FROM products
WHERE name ILIKE '%MUG%';