LIKE, ILIKE and pattern matching

Matches text against a pattern with % (any run of characters) and _ (exactly one character).

Syntax

text [NOT] LIKE 'pattern' [ESCAPE '\']
text [NOT] ILIKE 'pattern'          -- case-insensitive
text ~ 'regex' | text ~* 'regex'    -- POSIX regular expressions

% matches any sequence of zero or more characters; _ matches exactly one character.
LIKE is case-sensitive in PostgreSQL. ILIKE is the case-insensitive version (PostgreSQL extension); lower(col) LIKE 'abc%' is the portable alternative.
A pattern without wildcards is just equality.
To match a literal % or _, escape it: LIKE '%10\%%' (backslash is the default escape character).
~, ~*, !~, !~* match POSIX regular expressions (case-sensitive / insensitive). SIMILAR TO also exists but is rarely used.
NULL: NULL LIKE 'x%' is NULL, so rows with NULL never match, and NOT LIKE does not return them either.
Leading-wildcard patterns ('%abc') cannot use a normal B-tree index.

Examples

Starts with / contains starter

SELECT product_name
FROM products
WHERE product_name LIKE 'Dates%' OR product_name ILIKE '%coffee%'
ORDER BY product_name;

LIKE 'Dates%' is case-sensitive; ILIKE '%coffee%' matches Coffee and coffee anywhere in the name.

Single-character wildcard shop

SELECT DISTINCT region
FROM customers
WHERE region LIKE '_____ern'
ORDER BY region;

Exactly five characters before 'ern': Northern and Southern, not Eastern or Western.

Regular expression starter

SELECT email
FROM customers
WHERE email ~ '^[a-z]+[0-9]+@example\.com$'
ORDER BY email;

~ applies a POSIX regex; the dot is escaped to match a literal dot.

Pitfalls

Expecting LIKE to ignore case in PostgreSQL (it does not; use ILIKE or lower()).
Forgetting % entirely: LIKE 'Riyadh' only matches exactly 'Riyadh'.
Trailing spaces in data ('Khobar ') defeating = and LIKE 'Khobar'; trim() first.
Expecting NOT LIKE to return rows where the column is NULL.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: LIKE is case-insensitive under the default collations; no ILIKE. Regex via REGEXP / RLIKE.
SQL Server: Case sensitivity depends on the collation (usually insensitive); no ILIKE; [abc] character classes are allowed in LIKE.
SQLite: LIKE is case-insensitive for ASCII letters by default; no ILIKE.
Snowflake: Supports ILIKE and RLIKE / REGEXP_LIKE.
BigQuery: LIKE is case-sensitive; no ILIKE; use REGEXP_CONTAINS for regex.