LIKE & Wildcards (% and _)

Sometimes you only know part of the text: "names starting with J", "names containing 'ar'". = needs the full exact value, so we use LIKE with wildcards.

Definition

LIKE tests whether a text value matches a pattern. A pattern is a string containing ordinary characters (which must match exactly) and wildcard characters (placeholders that stand for unknown characters):

• % (percent) matches any sequence of zero, one or many characters.
• _ (underscore) matches exactly one single character.

LIKE must match the whole value: 'J%' means "J, then anything until the end". NOT LIKE keeps values that do not match.

Syntax

SELECT column_list
FROM table_name
WHERE column_name LIKE 'pattern';

WHERE column_name NOT LIKE 'pattern';

Pattern cheat sheet

Why does '%a' match only Rohit Sharma? The others end in i, r, i, r, h. And '%ar%' skips "Virat" because that has "ra", not "ar".

Example — names starting with J

SELECT *
FROM players
WHERE name LIKE 'J%';

'J%' = starts with J, then anything. Jos and Jasprit match.

Example — names containing 'ar'

SELECT *
FROM players
WHERE name LIKE '%ar%';

'%ar%' = "ar" anywhere in the name: Sharma and Warner. Because % also matches zero characters, '%ar%' would even match a name that is exactly "ar".

Example — _ for one character

SELECT name
FROM players
WHERE name LIKE '_o%';

_ consumes exactly the first letter (R, J), then the 2nd must be "o", then % takes the rest.

Searching for a real % or _

What if the text itself contains % or _, like a promo code 50%OFF or IPL_2025? Use an ESCAPE character to say "treat the next character literally":

WHERE code LIKE '%!%%' ESCAPE '!'      -- contains a real %
WHERE code LIKE 'IPL!_2025' ESCAPE '!' -- a real underscore

In SQL Server you can also wrap it in brackets: LIKE '%[%]%', LIKE 'IPL[_]2025'.

SQL Server extra wildcards

T-SQL LIKE supports two more wildcards (not in MySQL/PostgreSQL LIKE, not in this playground):

Case sensitivity

Common mistakes

• ❌ WHERE name = 'J%' → = treats % as a normal character. ✅ LIKE 'J%'.
• ❌ Using * or ? (Windows/Access wildcards). ✅ SQL uses % and _.
• ❌ LIKE 'J' expecting "starts with J" → without a wildcard it means exactly "J". ✅ 'J%'.
• ❌ Assuming '%a' = "contains a". ✅ it means ends with a; contains is '%a%'.

Performance note

LIKE 'J%' (fixed start) can use an index on name. A leading wildcard like LIKE '%ar%' forces SQL to check every row (a scan), which is slow on huge tables.

Interview tip

Q: What is the difference between % and _ in LIKE? A: % matches any number of characters including zero; _ matches exactly one character. So '_o%' means "second letter is o".