✴️Wildcards
% and _ are metacharacters, not literals
In LIKE patterns, % matches any run of characters (including empty) and _ matches exactly one character. User-supplied search strings that contain those characters can broaden matches unless you escape them or build patterns in code.
Escape literal percent
SELECT lot_code
FROM inventory
WHERE lot_code LIKE '50\%%' ESCAPE '\';| lot_code |
|---|
| 50%_PURE |
| 50%_STD |
⚡ Advanced / dialect note
'%needle') usually cannot use a B-tree index efficiently. For heavy search workloads consider trigram indexes (PostgreSQL pg_trgm) or full-text engines.🧭LIKE vs GLOB (SQLite)
Different metacharacters, do not mix mental models when reading dumps.
| Wildcard | SQL LIKE | Glob (SQLite) note |
|---|---|---|
| Any length | % | * with GLOB |
| Single char | _ | ? with GLOB |
User types 50% discount in search
Raw concatenation turns % into “match everything,” returning huge result sets or timing out.
Escape or parameterize pattern pieces
Escape % and _ from user input, or use a dedicated search layer with its own grammar.
Security + UX
Log slow LIKE queries separately, they are a common first step before someone adds a “quick” contains search that becomes an incident.
TL;DR
In LIKE patterns, % matches any length (including zero) and _ matches exactly one character; bracket classes [A-Z] match one character from a set. Wildcards are not the same as regular expressions, use SIMILAR TO or vendor regex operators when you truly need regex power. Leading % usually blocks index seeks, plan accordingly.