Mastering Pattern Matching with the LIKE Operator in SQL

Exact matches only get you so far. When you need “every name starting with A,” “any product ending in Mug,” or “anything containing scarf,” you need pattern matching — and in SQL that’s the LIKE operator. It lives in the WHERE clause and uses two wildcards to describe the shape of the text you’re after.

Try it live — this playground runs a real SQL engine in your browser. Edit the pattern, press Run, and watch which rows match.

Quick answer: what does LIKE do?

LIKE tests whether a text value matches a pattern built from two wildcards:

• % — matches any sequence of characters (including none).
• _ — matches exactly one character.

SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;

Everything else in the pattern is a literal that must appear as-is.

The two wildcards

Position the wildcards to describe where the match can vary:

% is the workhorse; _ is for when position matters and you want exactly one character to vary.

Matching multiple patterns at once

There is no single LIKE that takes a list of patterns in standard SQL — LIKE matches one pattern. To match several, chain them with OR, one LIKE per pattern:

SELECT name, category
FROM products
WHERE name LIKE '%Mouse' OR name LIKE '%Keyboard';

This is the answer to the common “SQL LIKE multiple values” question. A few databases add shortcuts — PostgreSQL has LIKE ANY (ARRAY['%a%','%b%']), Snowflake has LIKE ANY ('%a%','%b%'), and MySQL can fold patterns into one regex with REGEXP 'a|b' — but the portable answer is OR LIKE. And note: LIKE is for partial text. To match a column against a list of exact values, use the IN operator instead.

NOT LIKE — exclude a pattern

Put NOT in front to keep the rows that don’t match:

SELECT name
FROM products
WHERE name NOT LIKE '%e%';

That returns every product whose name has no letter “e” in it. NOT LIKE is handy for filtering out a family of values — test codes, temp names, a naming prefix you want to skip.

Case sensitivity and escaping

Two things that vary by database, worth knowing before a query surprises you:

• Case sensitivity. LIKE is case-insensitive by default in MySQL and SQL Server, but case-sensitive in PostgreSQL — there you’d use ILIKE for a case-insensitive match. (This playground matches case-insensitively, like MySQL.) When in doubt, normalize both sides with LOWER().
• Escaping wildcards. To match a literal % or _, use your database’s ESCAPE clause: WHERE code LIKE '50\% off' ESCAPE '\' finds the literal text “50% off” instead of treating % as a wildcard.

Practice

Frequently asked questions

What does the LIKE operator do in SQL? It matches text against a pattern using % (any run of characters) and _ (a single character). WHERE name LIKE 'A%' finds every name starting with A.

What’s the difference between % and _ in SQL LIKE? % matches any number of characters (including zero); _ matches exactly one. 'A_' matches a two-character value starting with A; 'A%' matches any value starting with A.

How do I match multiple values with LIKE? Chain one LIKE per pattern with OR: WHERE name LIKE '%Mouse' OR name LIKE '%Keyboard'. For exact values (not patterns), use IN instead.

Is SQL LIKE case-sensitive? It depends on the database and collation. MySQL and SQL Server are case-insensitive by default; PostgreSQL is case-sensitive (use ILIKE for case-insensitive).

How do I search for a literal % or _? Escape it with the ESCAPE clause: LIKE '50\% off' ESCAPE '\'.

From matching to moving data

Pattern matching is one filter among many. When your filtering, cleanup, and reshaping need to run continuously across sources, ET1 makes each step a node on a visual canvas — the LIKE you just wrote becomes a Filter node with a live preview of matching rows.

Keep going: filter one column against a list with the IN operator, match numeric or date ranges with BETWEEN, or combine conditions with AND, OR, and NOT.