Matching patterns with LIKE
LIKE finds text that matches a pattern. Two symbols stand in for unknown characters:
| Symbol | Matches | Example | Finds |
|---|---|---|---|
% | any number of characters (even none) | '%Paper%' | anything containing "Paper" |
_ | exactly one character | 'A_ Copier%' | "A4 Copier…", "A3 Copier…" |
SELECT name
FROM products
WHERE name LIKE '%Paper%'Where you put % matters:
LIKE 'Desk%': starts with "Desk"LIKE '%Ltd': ends with "Ltd"LIKE '%Desk%': contains "Desk" anywhere
Capital letters
In DuckDB and PostgreSQL, LIKE cares about capitals: '%paper%' won't find "Paper". DuckDB and
PostgreSQL also have ILIKE, which ignores capitals.
In other databases
SELECT name FROM customers WHERE name ILIKE '%bakery%'LIKE cares about capitals; ILIKE ignores them.
SELECT name FROM customers WHERE name LIKE '%bakery%'LIKE usually ignores capitals already (it depends on the database's collation). There's no ILIKE.
SELECT name FROM customers WHERE name LIKE '%bakery%'LIKE ignores capitals with the usual settings. There's no ILIKE.
SELECT name FROM customers WHERE name ILIKE '%bakery%'Like DuckDB: LIKE cares about capitals, ILIKE doesn't.
SELECT name FROM customers WHERE LOWER(name) LIKE '%bakery%'LIKE cares about capitals and there's no ILIKE, so lower-case both sides.