Skip to content
Common mistakes / SQL: Why LIKE '%_%' Matches Every Row
Common mistake

SQL: Why LIKE '%_%' Matches Every Row

In LIKE, _ matches any one character and % any run of characters. To match the characters themselves, escape them, or search with position().

The symptom

Marketing Campaigns names its ad groups with an id and an audience, such as AG-00212 in_market. You look for the names that contain an underscore with like '%_%', and all 60 ad groups come back, including AG-00059 remarketing, which has no underscore at all.

Why it happens

LIKE has two wildcards. % matches any run of characters, including none, and _ matches exactly one character, any character. So '%_%' means "at least one character", and every non-empty name has one. The same trap catches a search for '%50%%' when you meant "contains 50%": the second % is a wildcard too.

The fix

Escape the character to match it literally. ESCAPE '\' names the escape character, and \_ then means a real underscore:

Wrong: _ is a wildcard
select ad_group_name, target_audience
from ad_groups
where ad_group_name like '%_%'
order by ad_group_name
Right: escape it
select ad_group_name, target_audience
from ad_groups
where ad_group_name like '%\_%' escape '\'
order by ad_group_name

10 ad groups have an underscore: the in_market and custom_list audiences. Any character can be the escape, so like '%!_%' escape '!' does the same thing.

Or don't use LIKE at all

When you want to know whether a string contains another, with no pattern, skip the wildcards. position returns where the text starts, or 0 when it's missing, and treats every character literally:

No wildcards to escape 
select count(*) as with_underscore
from ad_groups
where position('_' in ad_group_name) > 0;

That returns 10. DuckDB also has contains(ad_group_name, '_'), and PostgreSQL has strpos.

Write ESCAPE every time

Engines disagree about the default. PostgreSQL and MySQL treat a backslash as the escape character without an ESCAPE clause. DuckDB doesn't: like '%\_%' with no clause looks for a backslash followed by any character, and here it finds 0 rows. SQL Server also accepts brackets, '%[_]%', which DuckDB reads as a literal [. An explicit ESCAPE works the same everywhere.

Same pattern, no ESCAPE clause 
select count(*) as matches
from ad_groups
where ad_group_name like '%\_%';

Learn more

Related

Lesson
SQL Regex Practice: regexp_matches and regexp_extract

Pull a size out of a product name with a pattern.

Reference
SQL String Functions

Length, case, trimming, slicing, and concatenation.

Reference
SQL Regular Expressions

Match, extract, and replace text by its shape.

Mistake
SQL: Why GROUP BY Splits One Value Into Two Groups

Spaces and capitals make one city two groups.

Mistake
SQL: Why regexp_replace Only Replaces the First Match

Pass 'g' to replace every match, not just the first.

Next · Why COALESCE(x, 0) drags an average down →