I thought % was only special at the ends, like %term%?

Every % in the pattern is a wildcard, including one the user typed in the middle of '100% off'. LIKE '100%' means 'starts with 100', not 'equals 100%'.

What does underscore do?

LIKE treats _ as exactly one character. A search for file_v2 matches fileXv2, file-v2, file_v2. That's why SKUs and filenames search 'wrong' without escaping.

How do I search for a literal %?

ESCAPE a character and prefix the wildcard: LIKE '%100\%%' ESCAPE '\' (syntax varies slightly). Or, if you meant exact match, use = '100%' and skip LIKE entirely.

Is this the same as case-sensitive search missing rows?

Opposite direction. Case sensitivity under-matches. Unescaped LIKE over-matches. You can have both: ILIKE '%100%' on Postgres is case-insensitive and still treats % as a wildcard.

When should the AI use = instead of LIKE?

When you asked for a specific code, SKU, email, or name and didn't say 'contains' / 'starts with.' LIKE is for patterns. = is for values.

SQL LIKE Wildcard % Underscore | Escape Search

You ask for products titled 100%. The AI writes WHERE title LIKE '%100%' — or worse, LIKE '100%' — and the list is enormous. % in LIKE means "any string," including the percent sign you thought you were looking for. _ is "any one character." Neither is a normal letter unless you escape it.

The pattern is not the string

| You meant | What often ran | What it actually matches | |---|---|---| | Title is 100% | LIKE '100%' | 100, 100 off, 1000, 100% | | Contains 100% | LIKE '%100%' | anything with 100 anywhere | | SKU file_v2 | LIKE '%file_v2%' | fileXv2, file-v2, file_v2 |

Valid SQL. No error. The result set is just too big, which is the same smell as AND/OR precedence — over-inclusion, not a type error.

Postgres ILIKE does not fix this. It only folds case. The wildcards stay wild. That's separate from case-sensitive =.

When LIKE is the wrong tool

If the question was "the product called 100%" or "SKU file_v2," you wanted =, not LIKE. Models reach for LIKE '%…%' because chat search feels like contains-search. For codes, emails, and exact titles, = (or IN) is the query.

If you did want contains, escape user text before it becomes a pattern:

-- Postgres: treat % and _ as literals, still wrap for contains
WHERE title LIKE '%' || replace(replace('100%', '\', '\\'), '%', '\%') || '%'
ESCAPE '\'

You do not need to memorize that. You need the model to not paste raw user text into LIKE.

How to catch it

If a search for a specific token returns a huge fraction of the table:

Look at the SQL — is it LIKE with a % or _ that came from the question?
Re-run as = on the exact value
Compare counts

A 2-row exact match vs a 2,000-row LIKE is this bug, not "the catalog is messy."

The prompt that heads this off

Use = for exact codes, SKUs, titles, and emails unless I say contains
or starts with. If you use LIKE / ILIKE, escape % and _ in the user
string so they are literals. Say whether the pattern is exact, prefix,
or contains.

The short version

% and _ are operators inside LIKE, not decoration. Searching for 100% without escaping is a prefix search for 100. If the result set exploded, read the pattern before you read the table.

Read-only MCP gateway: mcpserver.design.

Related reading: