SQL Pattern Matching And Collation

SQL Pattern Matching and Collation

"It is similar to LIKE, except that it interprets the pattern using the SQL standard's definition of a regular expression." — PostgreSQL — SIMILAR TO

"Nondeterministic comparison may determine strings to be equal even if they consist of different bytes. Typical situations include case-insensitive comparison, accent-insensitive comparison..." — PostgreSQL — Collation Support

Text matching looks like the simplest thing in SQL and is one of the least portable. LIKE itself is universal, but whether it cares about case is not a property of the operator — it is a property of the collation in force, and that default differs from engine to engine. The same WHERE name LIKE 'abc' matches 'ABC' on MySQL and SQLite but not on PostgreSQL. This skill builds on the set-and-three-valued-logic floor in sql-relational-and-null-discipline (a LIKE against a NULL column is UNKNOWN and dropped by WHERE, exactly like any other predicate) and routes string-function and index-cost concerns to their owners.

1. LIKE Wildcards % and _, and ESCAPE

LIKE has exactly two wildcards. "An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters" (PostgreSQL — LIKE). Every major engine agrees: in MySQL "% matches any number of characters, even zero characters. _ matches exactly one character" (MySQL — String Comparison).

To match a literal % or _, escape it. "To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause" (PostgreSQL — LIKE).

-- WRONG — the _ is a wildcard; this matches 'first_name', 'firstXname', 'first name'…
SELECT * FROM columns_meta WHERE name LIKE 'first_name';

-- WRONG — searching for a literal 90% discount code; % matches anything, so every row matches
SELECT * FROM codes WHERE code LIKE '90%';

-- RIGHT — escape the wildcard and declare the escape char explicitly
SELECT * FROM columns_meta WHERE name LIKE 'first\_name' ESCAPE '';
SELECT * FROM codes        WHERE code LIKE '90\%'        ESCAPE '';

The default escape character is not portable: PostgreSQL and MySQL default it to backslash, but SQLite has no default — "If the optional ESCAPE clause is present..." is the only way to get one there (SQLite — LIKE). Always write ESCAPE explicitly when matching literal wildcard characters.

2. The Centerpiece: LIKE Case Sensitivity Is Collation-Dependent and Not Portable

This is the single most damaging misconception about LIKE. Whether LIKE is case-sensitive is decided by the collation, and collations "are taken into account... by pattern matching operators" (PostgreSQL — Collation). Because each engine ships a different default collation, the same query returns different rows:

PostgreSQL exposes a separate keyword precisely because bare LIKE is case-sensitive there: "The key word ILIKE can be used instead of LIKE to make the match case-insensitive" (PG — LIKE). MySQL is the opposite by default: "string comparisons are not case-sensitive unless one of the operands... uses a case-sensitive collation or is a binary string" — SELECT 'abc' LIKE 'ABC' returns 1 (MySQL). SQLite folds case only for ASCII: "'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE" (SQLite).

-- WRONG — relies on an UNSTATED case behavior; returns different rows per engine.
-- Matches 'José','JOSÉ','josé' on MySQL; only the exact case on PostgreSQL.
SELECT * FROM people WHERE name LIKE 'jose%';

Make the intent explicit instead of inheriting the engine default. Two portable levers:

-- RIGHT (a) — fold both sides with LOWER() so case is irrelevant on every engine
SELECT * FROM people WHERE LOWER(name) LIKE LOWER('jose%');

-- RIGHT (b) — attach a collation to the operation so the rule is stated in the query
SELECT * FROM people WHERE name COLLATE "und-x-icu" LIKE 'jose%';   -- PG/ICU spelling

COLLATE "allows specifying the sort order and character classification behavior of data per-column, or even per-operation" (PG — Collation) — so you can pin case behavior on a single comparison. Index cost: wrapping the column in LOWER(name) (option a) makes the predicate non-sargable — a plain B-tree index on name can no longer be used, forcing a scan unless you build a matching expression/functional index. That trade-off and its fix (functional index on LOWER(name), or a case-insensitive collation on the column) are owned by sql-indexing-and-sargability — route there.

3. SIMILAR TO Is the SQL-Standard Regex; ~ / REGEXP / RLIKE Are Not

When LIKE's two wildcards aren't enough, the standard escalation is SIMILAR TO: "It is similar to LIKE, except that it interprets the pattern using the SQL standard's definition of a regular expression" (PG — SIMILAR TO). Like LIKE, it is whole-string anchored: "the SIMILAR TO operator succeeds only if its pattern matches the entire string; this is unlike common regular expression behavior where the pattern can match any part of the string" (PG).

-- RIGHT (standard regex) — whole-string match: a 3-letter code of A–Z
SELECT * FROM items WHERE code SIMILAR TO '[A-Z]{3}';

-- NON-STANDARD (PostgreSQL only) — POSIX regex, substring match, NOT in the SQL standard
SELECT * FROM items WHERE code ~ '[A-Z]{3}';

Everything regex-flavored that isn't SIMILAR TO is a vendor extension. PostgreSQL's POSIX operators are explicit about it: "The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE... All of these operators are PostgreSQL-specific" (PG — POSIX). MySQL and SQLite instead spell regex as REGEXP / RLIKE. None of these is portable; SIMILAR TO itself is supported only on some engines (notably PostgreSQL) and is rarely seen in practice. Route dialect spellings to sql-standard-vs-dialect-map.

4. ILIKE Is a Non-Standard PostgreSQL Extension

ILIKE is convenient and not portable. The documentation is explicit: case-insensitive ILIKE "is not in the SQL standard but is a PostgreSQL extension" (PG — LIKE). It also "does not support nondeterministic collations." Reach for the portable levers from §2 instead.

-- WRONG — ILIKE is PostgreSQL-only; fails to parse on MySQL, SQLite, Oracle
SELECT * FROM people WHERE name ILIKE 'jose%';

-- RIGHT — portable case-insensitive matching
SELECT * FROM people WHERE LOWER(name) LIKE 'jose%';

5. COLLATE for Comparison, Ordering, and Accent Sensitivity

Collation governs more than case — it decides accent sensitivity and the sort order of every comparison. "When the database system has to perform an ordering or a character classification, it uses the collation of the input expression" (PG — Collation). Case-insensitive and accent-insensitive matching are realized through a nondeterministic collation, which "may determine strings to be equal even if they consist of different bytes... case-insensitive comparison, accent-insensitive comparison" (PG — Collation).

The accent trap bites searches over names: under an accent-sensitive collation, 'José' and 'Jose' are different strings, so a user typing Jose finds nothing.

-- WRONG — accent-sensitive default collation: 'Jose' never matches stored 'José'
SELECT * FROM people WHERE name = 'Jose';

-- RIGHT — compare under an accent- and case-insensitive (nondeterministic) collation
SELECT * FROM people WHERE name = 'Jose' COLLATE "und-u-ks-level1";

COLLATE also pins a deterministic, reproducible ORDER BY instead of inheriting whatever the column/database default happens to be — important when output ordering must be stable across environments (a report sorted under en_US and another under C/byte-order disagree on where punctuation and case fall).

-- WRONG — sort order depends on the column's default collation; differs across DBs
SELECT name FROM people ORDER BY name;

-- RIGHT — state the collation so the ordering is reproducible everywhere
SELECT name FROM people ORDER BY name COLLATE "en-x-icu";

The base sort-order and character-classification concepts are deepened in sql-relational-and-null-discipline (ordering) and the dialect-specific collation names live in sql-standard-vs-dialect-map.

6. Leading-Wildcard LIKE '%term%' Cannot Use an Index

A LIKE whose pattern starts with a wildcard — '%term' or '%term%' — cannot use an ordinary B-tree index, because the index is ordered by leading characters and a leading % leaves no prefix to seek on. The result is a full table (or index) scan that degrades badly as the table grows. A trailing-only wildcard ('term%') is sargable and can range-scan an index.

-- SLOW — leading wildcard forces a scan of every row
SELECT * FROM articles WHERE body LIKE '%postgres%';

-- SARGABLE — anchored prefix can use a B-tree index on body
SELECT * FROM articles WHERE title LIKE 'postgres%';

This is a sargability problem, not a correctness one. The fixes — prefix-anchored patterns, trigram/GIN indexes, or a real full-text search index — are owned by sql-indexing-and-sargability; route there for the substring-search performance pattern.

7. Portability Snapshot

LIKE and its %/_ wildcards are universal; almost everything else here varies:

The portable kit: LIKE with explicit ESCAPE; LOWER() on both sides (or a stated COLLATE) for case-insensitive search; SIMILAR TO only if your engine supports it. For every dialect spelling — ILIKE, ~/~*, REGEXP/RLIKE, GLOB, collation names — route to sql-standard-vs-dialect-map.

8. Who Suffers When Matching Is Mishandled

A matching bug rarely errors — it returns the wrong rows, and someone downstream pays:

• The engineer who tested WHERE name LIKE 'admin%' on a local SQLite database, watched it match 'Admin', shipped it, and then saw it miss every capitalized name in PostgreSQL production — the case behavior silently flipped between engines (§2). The query was "valid" on both; it just meant different things.
• The support agent whose customer "José" can't find his own account because the search box runs name = 'Jose' under an accent-sensitive collation, and 'Jose' ≠ 'José' (§5). No error, no result, an angry customer.
• The analyst whose LIKE '%term%' report ran in 40 ms against ten thousand rows in staging and times out against ten million in production, because the leading wildcard forced a full scan that no index could rescue (§6).
• The maintainer who copied a working ILIKE query from a PostgreSQL service into a MySQL one and got a syntax error at deploy time (§4).

Explicit matching is empathy in code: the LOWER() on both sides, the stated COLLATE, the escaped literal %, and the anchored prefix are all gifts to whoever runs this query on the next engine.

9. Routing to Related Skills

• sql-relational-and-null-discipline — the foundation: set semantics and three-valued logic; a LIKE against NULL is UNKNOWN and dropped by WHERE, and ORDER BY/collation ordering basics.
• sql-expressions-case-and-functions — general string functions (LOWER, UPPER, TRIM, SUBSTRING, ||/CONCAT) used to build and normalize the strings you match on.
• sql-indexing-and-sargability — why LOWER(col) and leading-wildcard LIKE '%x%' are non-sargable, and the functional-index / trigram / full-text fixes (§2, §6).
• sql-data-types-and-numerics — CHAR vs VARCHAR and trailing-space/padding semantics that interact with comparison and matching.
• sql-standard-vs-dialect-map — dialect spellings: ILIKE, POSIX ~/~*, REGEXP/RLIKE, GLOB, and collation names per engine.

10. Reference Files

High-frequency pattern-matching and collation anti-patterns in LLM-generated SQL, each with wrong/right code and a primary-source citation:

references/common-mistakes.md

Source provenance for every claim in this skill:

references/sources.yaml