Is the SQL LIKE predicate case-sensitive, and what actually decides that?
answer
- it is not the operator's decision
- the same query differs across engines
- string comparison rules attached to the column
- think collation and engine defaults
basics
~20 sLIKE itself decides nothing about case. The comparison follows the collation of the strings involved, and engines ship different defaults, so the same LIKE query can be case-sensitive on one database and not on another.
solid answer
~40 sCase behaviour is a property of the **collation**, not of `LIKE`. The standard defines the predicate in terms of character comparison under the operands' collation, so whether `'Anna' LIKE 'ann%'` is true depends on the collation attached to the column, the database, or an explicit `COLLATE` on the expression. Defaults genuinely differ: PostgreSQL's usual collations are case-sensitive; MySQL's default `utf8mb4` collations end in `_ci` and are case-insensitive; SQL Server depends on the database or column collation, and many installations are case-insensitive; SQLite's `LIKE` is case-insensitive for ASCII characters by default. The portable way to force one behaviour is to normalise both sides — `WHERE LOWER(name) LIKE LOWER(:pattern)` — accepting that you now have an expression on the column. Do not assume; check the collation.
code
sql · 7 lines-- Collation-dependent: may or may not return 'Anna'
SELECT name FROM customers
WHERE name LIKE 'ann%';
-- Portable case-insensitive form: both sides folded
SELECT name FROM customers
WHERE LOWER(name) LIKE LOWER('ann%');go deeper
Know that whether LIKE distinguishes A from a is not fixed by SQL itself, and that folding both sides with LOWER is the simple way to get predictable behaviour.
Explain that collation defines character equality for the comparison, name where a collation can be attached, and give a correct example of two engines whose defaults differ.
Demonstrate diagnosis: when a search returns different rows in two environments, check collations before blaming the data, and decide whether normalising belongs in the query or in the schema.
Own the standard across services: one decision about which columns are case-insensitive, enforced in the schema, beats every team folding case differently in their own queries and drifting apart.
## The short answer, stated precisely `LIKE` is not case-sensitive or case-insensitive. It compares characters using the **collation** in effect for its operands, and the collation is what defines whether `A` and `a` are considered the same character. Two databases running the identical query can therefore return different rows, and neither is violating the standard. ## What a collation is A collation is a set of rules attached to character data that says how strings compare and sort: which characters are equal, in what order they sort, and how accents and case are handled. It can be attached at several levels — server, database, table, column, or a single expression via a `COLLATE` clause — with the most specific level winning. Collation names are engine-specific, which is precisely why this is a portability question rather than a syntax question. ## Where the engines actually stand Stating this carefully matters, because a wrong claim here is how people ship broken searches: - **PostgreSQL** uses case-sensitive collations by default, so `LIKE` is case-sensitive. - **MySQL**'s default `utf8mb4` collations are `_ci` (case-insensitive) ones, so `LIKE` is case-insensitive on a default installation. - **SQL Server** takes the database or column collation; many installations are configured case-insensitive, but it is a per-installation fact you must check rather than assume. - **SQLite** documents `LIKE` as case-insensitive for ASCII characters by default; non-ASCII characters are not folded. The consequence: a migration between engines, or between two databases created with different collations, can silently change which rows a search returns. Nothing errors; the result set just differs. ## Forcing the behaviour you want There are three portable-ish strategies. **Normalise both sides.** The most portable is to fold the case yourself: ```sql WHERE LOWER(name) LIKE LOWER(:pattern) ``` This behaves the same everywhere, at the cost of putting an expression on the column — which changes what access paths the engine can use, a question for the indexing discussion rather than this one. Note also that case folding is locale-dependent for some alphabets (the Turkish dotless i is the classic example), so `LOWER` is not a perfect universal equaliser for non-ASCII text. **Attach an explicit collation.** Most engines let you write a `COLLATE` clause on the comparison to pin the behaviour for that one predicate. The clause is widely supported, but the collation *names* are engine-specific, so this pins behaviour at the price of portability of the SQL text. **Fix it in the schema.** If the column is conceptually case-insensitive — usernames, email addresses, product codes — declare that once in the column's collation or store a normalised copy, so every query agrees automatically. This is usually the right answer for data that is compared for identity rather than searched as prose. ## Do not reach for engine-specific operators Some engines provide a dedicated case-insensitive pattern operator. Using one solves the problem on that engine and makes the statement non-portable. If portability matters, normalise; if it does not, at least be conscious that you are choosing a dialect feature. ## A related trap Case is only one dimension a collation controls. Accent sensitivity is another: under an accent-insensitive collation, `'café' LIKE 'cafe'` can be true. Candidates who have only ever thought about case are often surprised by that. The general lesson is the same — the pattern predicate does not define character equality; the collation does. ## What an interviewer is listening for The strong answer refuses the yes/no framing: "it depends on the collation" is the correct opening sentence, followed by an accurate example of two engines that differ and a concrete way to force the behaviour you need. The weak answer is a flat "LIKE is case-sensitive" or "LIKE is case-insensitive" — both are wrong as general claims, and both produce a search that behaves differently in production than it did on the developer's laptop.
- What is the most portable way to make a LIKE search case-insensitive on any engine?Normalise both operands yourself: `WHERE LOWER(name) LIKE LOWER(:pattern)`. It behaves identically everywhere because it no longer relies on the collation. The costs are that you have an expression on the column, which changes which access paths are available, and that case folding is locale-dependent for some non-ASCII alphabets. For identity-style columns, normalising at write time is often better.
- Besides case, what else can a collation change about whether a LIKE pattern matches?Accent and width sensitivity, and the treatment of certain equivalent character sequences. Under an accent-insensitive collation `'café' LIKE 'cafe'` can be true, which surprises people who only thought about case. The general point is that the collation defines character equality for the comparison; the pattern predicate just applies it.
- A search worked in staging and returned fewer rows in production. How would you check whether collation is the cause?Compare the collation in effect on both databases and on the specific column, then run the same pattern with both operands folded — for example `LOWER(col) LIKE LOWER(pattern)` — and see whether the counts converge. If the folded query returns the staging count in both environments, the difference is case handling from the collation, not the data.
saying these in an interview costs you the question
- Stating flatly that LIKE is case-sensitive in SQL
- Assuming every database behaves like the one on your laptop
- Thinking UPPER on only one side of the comparison fixes case
- Believing a case-insensitive operator from one engine is portable
- Ignoring that collation also governs accent sensitivity