Concept · SQL & Querying

LIKE matches patterns and its case-sensitivity is dialect-dependent; = is exact.

LIKE is a tiny pattern language with two symbols and one trap: the symbols are legal data. Searching for '100%' with LIKE '%100%' finds every string containing 100 — the trailing percent silently became a wildcard. ESCAPE names a prefix character that de-wildcards the next symbol. And _ is one-character-exactly, not a small %.

See it break

fabrics

name
linen 100%
cotton 200
linen blend
silk 100

The trap

SELECT
  name
FROM
  fabrics
WHERE
  name LIKE '%100%' ORDER BY
  name;

The fix

SELECT
  name
FROM
  fabrics
WHERE
  name LIKE '%100\%' ESCAPE '\'
ORDER BY
  name;

The trap

linen 100%

silk 100

The fix

linen 100%

The search for a literal '100%' with LIKE '%100%' matched 'silk 100' too — the trailing % is a wildcard, not a character, until ESCAPE de-wildcards it.

The rule

ESCAPE for literal symbols; ILIKE / LOWER() for case; anchor patterns deliberately (a leading % also kills the index — a P17 story).

Case sensitivity is dialect business — ILIKE where it exists, LOWER() both sides where it doesn't.

Shows up elsewhere

Sargability Implicit casts in comparisons

Try one →Prove it in practice →