LIKE and Pattern Matching
LIKE wildcards, the ESCAPE clause, collation-driven case sensitivity, and when to switch to REGEXP_LIKE instead.
expr LIKE pat [ESCAPE 'escape_char'] -- % = any run of characters, _ = exactly oneexpr LIKE pat matches a string against a pattern and returns 1 or 0, or NULL if either operand is NULL. Two wildcards: % matches any number of characters including zero, and _ matches exactly one. The pattern must cover the whole value, so '%w%' is how you say "contains w".
Escaping
If you do not write an ESCAPE clause, \ is assumed, unless the NO_BACKSLASH_ESCAPES SQL mode is on, in which case no escape character is used. \% matches a literal percent sign and \_ a literal underscore. ESCAPE takes a one-character constant (or an empty string, meaning no escape character) — 'David_' LIKE 'David|_' ESCAPE '|' returns 1.
Case sensitivity comes from the collation
String comparison is not case-sensitive unless one operand uses a case-sensitive collation or is a binary string. MySQL 8.4's server default is utf8mb4_0900_ai_ci, which is accent- and case-insensitive, so LIKE 'pro%' already matches Professional. Force a distinction with COLLATE utf8mb4_0900_as_cs.
REGEXP
REGEXP and RLIKE are synonyms for REGEXP_LIKE(expr, pat[, match_type]), implemented with ICU. Unlike LIKE, a regex matches if it hits anywhere in the value, so use ^ and $ to anchor. Binary strings are rejected with ER_CHARACTER_SET_MISMATCH.
Example
-- Find products whose name literally contains "50%".
-- Unescaped, the % is a wildcard and this matches anything containing "50".
SELECT id, name
FROM products
WHERE name LIKE '%50\%%'; -- \ is the default escape character
-- Same search with an explicit escape character. Clearer when the pattern
-- is assembled by application code that may itself contain backslashes.
SELECT id, name
FROM products
WHERE name LIKE '%50!%%' ESCAPE '!';
-- Case-sensitive prefix match. The default collation utf8mb4_0900_ai_ci
-- ignores case and accents, so plain LIKE 'Pro%' also matches 'professional'.
-- Note that COLLATE on the column prevents use of an index on name.
SELECT id, name
FROM products
WHERE name COLLATE utf8mb4_0900_as_cs LIKE 'Pro%';When to use it
- Implement a "starts with" autocomplete that can still use the index on the column
- Search for a literal underscore or percent sign inside a product code
- Make one search case-sensitive without changing the column's collation
- Validate an email or phone format inside a query with REGEXP_LIKE
More examples
Wildcards, anchoring and index use
The shape of the pattern decides the query plan. A pattern with a fixed prefix leaves a range for the index to seek; one starting with % does not. When you genuinely need suffix search, store and index a reversed copy of the column, or move the work to a full-text index.
-- Prefix match: can use an index on email (a range scan on the prefix).
SELECT id FROM users WHERE email LIKE 'ada@%';
-- Leading wildcard: no index prefix to seek on, so this scans.
SELECT id FROM users WHERE email LIKE '%@example.com';
-- Exactly five characters, nothing more, nothing less.
SELECT id FROM users WHERE username LIKE '_____';
-- NOT LIKE is just the negation, and is NULL for a NULL email.
SELECT id FROM users WHERE email NOT LIKE '%@example.com';REGEXP, RLIKE and REGEXP_LIKE are one function
REGEXP and RLIKE are operator spellings of REGEXP_LIKE(), so use whichever reads better — but switch to the function form when you need match_type. The documented characters are c (case-sensitive), i (case-insensitive), m (multi-line), n (dot matches line terminators) and u (Unix-only line endings); if two contradict, the rightmost wins. Backslashes are doubled here because the SQL string literal consumes one.
-- These three are the same query.
SELECT id FROM users WHERE email REGEXP '@example\\.(com|org)$';
SELECT id FROM users WHERE email RLIKE '@example\\.(com|org)$';
SELECT id FROM users WHERE REGEXP_LIKE(email, '@example\\.(com|org)$');
-- Only the function form takes match_type: 'c' forces case-sensitive
-- matching regardless of the column's collation.
SELECT id FROM users WHERE REGEXP_LIKE(email, '^Admin', 'c');Escaping user input before it reaches the pattern
Parameter binding stops SQL injection but does nothing about wildcard characters: a bound value of '%' still matches every row. Build the pattern by escaping the escape character first, then % and _, and declare the escape character explicitly so the query does not depend on whether NO_BACKSLASH_ESCAPES is set on the server.
-- Escape the two wildcards and the escape character itself, then
-- wrap the result in % for a contains-search. ? is the driver's
-- placeholder for the raw user string.
SELECT id, name
FROM products
WHERE name LIKE CONCAT('%', REPLACE(REPLACE(REPLACE(?,
'!', '!!'),
'%', '!%'),
'_', '!_'), '%')
ESCAPE '!';
Discussion