Español

Field manual · SQL traps

Why doesn't LIKE find a name that's right there in the table?

LIKE compares character by character and is case-sensitive. If the real data comes in uppercase, lowercase or without accents, a well-written pattern misses rows sitting right in front of it.

Where it shows up

Free-text search over names, addresses or any field people type by hand: case and accents are never consistent across systems.

Example

Wrong: LIKE is case-sensitive

SELECT *
FROM guests
WHERE name LIKE '%Vela%';
-- the front desk says Vela stayed 3 nights; this only finds 1:
-- 'VELA, ANDRES' and 'vela andres' don't match '%Vela%'

Right: ILIKE, case-insensitive

SELECT *
FROM guests
WHERE name ILIKE '%vela%';

LIKE is case-sensitive in DuckDB (like in most engines, unless the column uses a special collation): 'VELA, ANDRES' LIKE '%Vela%' is false, letter for letter, not an approximation. ILIKE runs the same comparison while ignoring case. There's a sibling problem ILIKE doesn't fix: accents. 'andres' ILIKE '%andrés%' is also false, because the engine cares about the accent mark as much as the case; that needs normalizing both sides with strip_accents() before comparing. Neither engine warns you when a pattern finds nothing: zero rows reads as 'nobody matches', not as 'you searched wrong'.

The game is full of traps like this one, hiding inside real case files with real data.

Play the first case free →

Related traps