MySQL – LIKE and Pattern Matching

LIKE matches a string against a pattern. It has exactly two wildcards, which makes it easy to learn and easy to outgrow — this lesson covers both, plus REGEXP for when they are not enough, and the performance rule that decides whether your search can use an index.

The two wildcards

% on both sides is "contains":

SELECT name FROM product WHERE name LIKE '%Chicken%' ORDER BY id;

+-----------------------+
| name                  |
+-----------------------+
| BBQ Chicken Pizza     |
| Buffalo Chicken Pizza |
+-----------------------+

Anchored on the left, it is "starts with":

SELECT name FROM product WHERE name LIKE 'P%' ORDER BY id;

+-----------------+
| name            |
+-----------------+
| Pepperoni Pizza |
| Pepsi           |
+-----------------+

_ stands for one character and no more:

SELECT name FROM product WHERE name LIKE '_iet%' ORDER BY id;

+------------+
| name       |
+------------+
| Diet Pepsi |
+------------+

A pattern with no wildcard at all is just an equality test.

Case sensitivity is the collation's business

LIKE 'p%' returns the same two rows as LIKE 'P%' above. That is not a property of LIKE — it is the column's collation, utf8mb4_0900_ai_ci, where ci means case-insensitive (and ai means accent-insensitive, so e matches é too).

On a case-sensitive collation the same query would return only the capitalised rows. If you need a case-sensitive match regardless of the column, force it:

SELECT name FROM product WHERE name LIKE 'p%' COLLATE utf8mb4_0900_as_cs;

Do not reach for UPPER(name) LIKE 'P%' to control this. It gives the wrong answer about intent and, as below, it costs you the index.

Matching a literal % or _

Both wildcards are ordinary characters in real data — percentages in a description, underscores in a slug. ESCAPE names a character that turns off the next one:

-- rows whose description contains a real "50%", not "50 followed by anything"
SELECT name FROM product WHERE description LIKE '%50!%%' ESCAPE '!';

Backslash also works by default, but it is already an escape character in string literals, so you end up doubling it. Naming your own with ESCAPE is clearer.

NOT LIKE

SELECT name FROM product WHERE name NOT LIKE '%Pizza' ORDER BY id;

The usual NULL caveat applies: a row with a NULL name matches neither LIKE nor NOT LIKE.

REGEXP, when two wildcards are not enough

REGEXP (or its synonym RLIKE) takes a full regular expression:

SELECT name FROM product WHERE name REGEXP '^(Pep|Che)' ORDER BY id;

+-----------------+
| name            |
+-----------------+
| Pepperoni Pizza |
| Cheese Pizza    |
| Pepsi           |
+-----------------+

Alternation, character classes, anchors and repetition all work. It is strictly more powerful than LIKE and strictly slower — it can never use an index, so it always reads every row. Use it when the pattern genuinely needs it, not by default.

The performance rule

An index on a string column stores the values sorted. That makes a prefix search a range scan, and anything else a full scan:

It is the same reason a paper dictionary can find "pepper" quickly and cannot find words ending in "-roni": the ordering only helps from the left. On 14 products this costs nothing. On a large table a leading-% search reads every row every time.

When you genuinely need "contains" over a lot of text, LIKE is the wrong tool — that is what full-text search is for, and past a certain point, what a search engine is for.

What to remember

• % is any number of characters, _ is exactly one.
• Case sensitivity comes from the collation, not from LIKE.
• ESCAPE to match a literal % or _.
• LIKE 'x%' can use an index; LIKE '%x' and REGEXP cannot.