8.7 LIKE / NOT LIKE and Wildcards

Level: 8 | Version: v1.1 | Author: Meptrasoft

Overview

LIKE is used to match text against a pattern.

NOT LIKE is used to exclude text that matches a pattern.

Operator	Purpose
`LIKE`	Matches a pattern
`NOT LIKE`	Excludes a pattern

Two main wildcards are used:

Wildcard	Meaning
`%`	Any number of characters, including zero
`_`	Exactly one character

LIKE → Match a pattern
NOT LIKE → Exclude a pattern

SQL LIKE, NOT LIKE & Wildcards: Matching and excluding text patterns — Figure 1: LIKE matches text patterns, NOT LIKE excludes matching patterns, and % and _ define how text is matched.

Practice Table

This topic reuses the employees table from 8.1 The WHERE Clause.

emp_id	emp_name	dept_id	salary
1	John Smith	10	45000
2	Emma Brown	20	60000
3	Samuel Reed	10	32000
4	Anderson	30	55000
5	Sarah Wilson	20	48000
6	Tom Watson	10	51000

LIKE

LIKE matches text using a pattern.

Starts With

SELECT *
FROM employees
WHERE emp_name LIKE 'S%';

S% means:

Starts with S, followed by any number of characters.

This matches:

Samuel Reed
Sarah Wilson

Ends With

SELECT *
FROM employees
WHERE emp_name LIKE '%son';

%son means:

Ends with son.

For example:

Anderson

Contains

SELECT *
FROM employees
WHERE emp_name LIKE '%am%';

%am% means the text am can appear anywhere in the value.

LIKE → Find text that matches a pattern

NOT LIKE

NOT LIKE excludes values that match the pattern.

SELECT *
FROM employees
WHERE emp_name NOT LIKE '%son';

This returns names that do not end with son.

NOT LIKE → Exclude matching patterns

Wildcard `%`

% matches zero or more characters.

Examples:

'a%'    → Starts with a
'%a'    → Ends with a
'%a%'   → Contains a

For example:

SELECT *
FROM employees
WHERE emp_name LIKE 'J%';

matches names beginning with J.

Wildcard `_`

_ matches exactly one character.

Example:

SELECT *
FROM employees
WHERE emp_name LIKE '_a%';

Here:

_ → first character
a → second character
% → remaining characters

So the second character must be a.

Common Patterns

Pattern	Meaning
`'S%'`	Starts with `S`
`'%son'`	Ends with `son`
`'%am%'`	Contains `am`
`'S_'`	Starts with `S` and has exactly 2 characters
`'_a%'`	Second character is `a`
`'__a%'`	Third character is `a`

Easy Memory Trick

% → MANY / ANY CHARACTERS
_ → ONE CHARACTER

LIKE vs NOT LIKE

LIKE
→ MATCH THE PATTERN

NOT LIKE
→ EXCLUDE THE PATTERN

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
`%` means exactly one character	`%` means zero or more characters
`_` means any number of characters	`_` means exactly one character
`LIKE 'S%'` finds names containing S anywhere	It finds values starting with `S`
`LIKE '%son'` finds values containing `son` anywhere	It finds values ending with `son`
`NOT LIKE` removes data	It only filters the query result
`=` and `LIKE` always behave the same	`LIKE` is used for pattern matching

Placement Quick Points

LIKE
→ MATCH PATTERN

NOT LIKE
→ EXCLUDE PATTERN

%
→ ZERO OR MORE CHARACTERS

_
→ EXACTLY ONE CHARACTER

LIKE is used for pattern matching.
NOT LIKE excludes matching patterns.
% matches zero or more characters.
_ matches exactly one character.
LIKE 'S%' finds values starting with S.
LIKE '%son' finds values ending with son.
LIKE '%am%' finds values containing am.

Interview Questions

What is LIKE in SQL?: LIKE is used to match text against a specified pattern.
What is NOT LIKE?: NOT LIKE returns rows whose values do not match the specified pattern.

Practice & Hands-On Exercises

Using the employees table:

Find employees whose name starts with S:

SELECT * FROM employees WHERE emp_name LIKE 'S%';

Find employees whose name ends with son:

SELECT * FROM employees WHERE emp_name LIKE '%son';

Find employees whose name contains am:

SELECT * FROM employees WHERE emp_name LIKE '%am%';

Find employees whose name does not end with son:

SELECT * FROM employees WHERE emp_name NOT LIKE '%son';

Find names where the second character is a:

SELECT * FROM employees WHERE emp_name LIKE '_a%';

Explain the difference between % and _.
Explain the difference between LIKE and NOT LIKE.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

LIKE    → MATCH
NOT LIKE → EXCLUDE

% → ANY NUMBER OF CHARACTERS
_ → EXACTLY ONE CHARACTER

LIKE and NOT LIKE are used for text pattern matching, while % and _ define how the pattern should match.

End of LIKE / NOT LIKE and Wildcards