← All Kits · SQL Kit

LIKE in SQL: Pattern Matching for Beginners

The tool for searching inside a text column, not just matching it · Part of the Analyst Prep Kit

By Michael Nocito, data analyst · Updated August 19, 2026

Most of the time you ask SQL for an exact value. WHERE status = 'Active' is a closed question with a yes or no answer. But a lot of real columns do not hold one tidy value. They hold a sentence, a product name with a code stuck on the front, an email address, or a list of tags jammed into a single cell. For those you need a looser question, and LIKE is how you ask it. It is a small piece of syntax with exactly two moving parts, and once those click you can search inside text instead of only matching it.

Why equals asks a stricter question than you want

Take a products table with a name column:

You want the shirts. The obvious thing fails:

SELECT name
FROM products
WHERE name = 'Shirt';

That returns one row, id 4, because = compares the whole value start to finish. Blue Cotton Shirt is not the same string as Shirt, so it is excluded, and correctly so. Equals is doing its job. It is just the wrong question.

The question you actually have is "does the name contain the word Shirt anywhere in it?" That is a pattern, and patterns are what LIKE is for:

SELECT name
FROM products
WHERE name LIKE '%Shirt%';

Three rows come back: Blue Cotton Shirt, Shirt Stay Clips, and Shirt.

The two wildcards, % and _

Everything LIKE can do comes from two characters.

The "including none at all" part of % is the bit people miss. 'S%' matches the single letter S, because % is allowed to stand for nothing. The underscore is not so forgiving. It demands a character be there.

Say this one out loud before you read the answer: what would '_at' match, and would flat be in the list? Commit to an answer first.

It matches three-character values ending in at, so cat, hat, bat. flat is out, because _ covers one character and flat has two before the at. If you wanted both you would write '%at'.

You can mix them. 'A__-%' matches a value that starts with A, has exactly two more characters, then a hyphen, then anything. That is a useful shape for product codes and account numbers, where the format is fixed but the content is not.

Where you put the % is the whole question

Same wildcard, four positions, four completely different questions. This is the table worth memorising:

A LIKE pattern with no wildcard in it is just a slower =. If you write LIKE 'Shirt' you have asked for an exact match and should use = instead, which is clearer to whoever reads the query next.

NOT LIKE, and finding what is missing

Put NOT in front and you get the opposite set:

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

That returns the rows with no Shirt in them. This is more useful than it first looks, because "which rows do not fit the expected shape" is one of the most productive questions in data cleaning. Emails with no at sign, product codes missing their prefix, a text column where some rows hold a list and some do not. NOT LIKE finds the odd ones out.

Case sensitivity, which changes by database

Whether '%shirt%' finds Blue Cotton Shirt depends on which database you are sitting in. This surprises people who move between two of them, so it is worth checking once rather than assuming.

The portable move, if you want the same answer everywhere, is to flatten both sides yourself:

SELECT name
FROM products
WHERE LOWER(name) LIKE '%shirt%';

That works in every database listed above, at the cost of a little speed. See the last section for why.

The over-matching trap

Predict this one before reading on. You search a genre column with LIKE '%Video%'. What comes back?

In a real Steam games dataset, that pattern matches both Video Production and 360 Video, because both contain those five letters. That may be exactly what you wanted, or it may quietly double your result set.

% does not know about word boundaries. It matches letters, not meaning. Some classics:

The fix is to give the pattern more to hold on to. If the values are separated by a known character, include it: '%, Sales%' is far more precise than '%Sales%'. The next section shows the reliable version of that trick.

Searching a column that holds a list

This is where analysts reach for LIKE most often. Some columns pack several values into one cell, separated by commas:

= is useless here, because almost no cell holds only RPG. LIKE handles it without any splitting:

SELECT name
FROM games
WHERE genres LIKE '%RPG%';

That finds Game A and Game C, whether the tag sits first, last, or buried in the middle.

The precise version guards against the over-matching trap. Wrap the value in the separator on both sides, then search for the separated tag:

SELECT name
FROM games
WHERE ',' || genres || ',' LIKE '%,RPG,%';

The || glues text together, so Action,Indie,RPG temporarily becomes ,Action,Indie,RPG,. Now every tag has a comma on both sides, including the first and last, and the pattern '%,RPG,%' can only match a whole tag. A genre called RPGMaker would no longer be a false hit. Use CONCAT(',', genres, ',') instead of || in MySQL and SQL Server.

That column shape causes more trouble than just searching, including a distinct count that comes back wrong and never says so. One Cell, Many Values covers the whole problem.

Matching a literal % or _

Every so often the character you are hunting for is a wildcard. Searching a notes column for an actual percent sign with LIKE '%%%' matches every row, which is not useful. Declare an escape character and mark the literal one:

SELECT note
FROM feedback
WHERE note LIKE '%!%%' ESCAPE '!';

Read that pattern as: anything, then a real percent sign (the one after the !), then anything. The ESCAPE '!' at the end is what tells SQL that ! is the marker. You can pick any character for the job as long as it does not appear in your data. The same applies to a literal underscore, which matters more than you would think, because underscores are everywhere in column names and product codes.

Why LIKE '%text%' gets slow

An index on a text column works like the index at the back of a book: entries sorted alphabetically, so the database can jump straight to the right place. That works when you know how the value starts.

On a few thousand rows this is invisible. On tens of millions it is the difference between an instant answer and a coffee break. It is not a reason to avoid '%text%', it is a reason to know why the query got slow when the table grew. If you need fast searching inside text at scale, that is a full text search index, a different tool. Indexing for Analysts covers what an index can and cannot do.

The same logic explains the cost of LOWER(name) LIKE ... from earlier. Wrapping the column in a function means the index on that column no longer applies, because the index stores the original values, not the lowercased ones.

A LIKE cheat sheet