Wildcards

Learn the % and _ wildcard characters used with LIKE to build precise text search patterns.

Introduction

The previous lesson introduced LIKE and used the % wildcard to match names starting with a letter. This lesson takes a full, dedicated look at both wildcard characters MySQL supports with LIKE, so you can build precise patterns for any text search.

We will keep using the students table with columns id and name.

id	name
1	Alex
2	Arjun
3	Priya
4	John
5	Amit
6	Sana
7	Sanjay

What You Will Learn

What the % wildcard matches, and how it can appear anywhere in a pattern.
What the _ wildcard matches, and how it differs from %.
A table of common pattern examples and exactly what each one matches.
How to escape a literal % or _ character when you need to match it as text.

The % Wildcard

The % wildcard matches zero or more characters of any kind. It can appear at the start, end, middle, or even multiple times within a single pattern.

SELECT name FROM students WHERE name LIKE 'A%';   -- starts with A
SELECT name FROM students WHERE name LIKE '%a';   -- ends with a
SELECT name FROM students WHERE name LIKE '%an%'; -- contains "an" anywhere

Because % can match zero characters, a pattern like 'A%' also matches a name that is exactly 'A' with nothing after it.

The _ Wildcard

The underscore wildcard, _, matches exactly one character — no more, no less. It is useful when you know the length of part of a pattern, but not the exact characters.

SELECT name FROM students WHERE name LIKE '_ohn';

The pattern '_ohn' means "exactly one unknown character, followed by ohn." This matches 'John' because J is one single character before 'ohn', but it would not match a name like 'Bhojohn', since the total pattern length would not line up.

Pattern Examples

The table below shows common patterns applied to our students table and exactly which names each one matches.

Pattern	Meaning	Matches
'A%'	Starts with A, then anything	Alex, Arjun, Amit
'%a'	Ends with a	Priya, Sana
'%an%'	Contains "an" anywhere	Sana, Sanjay
'_ohn'	Exactly one character, then "ohn"	John

Example: Combining Both Wildcards

Both wildcards can be used together in a single pattern. Suppose you want every name that starts with 'S', has any single character next, then contains 'n' immediately after that.

combined_wildcards.sql

SELECT name
FROM students
WHERE name LIKE 'S_n%';

Result

+---------+
| name    |
+---------+
| Sana    |
| Sanjay  |
+---------+

The pattern 'S_n%' breaks down as: 'S' (a literal letter), '_' (exactly one unknown character), 'n' (a literal letter), then '%' (anything after that, including nothing). 'Sana' matches because S-a-n fits the fixed part and 'a' fills the %. 'Sanjay' matches for the same reason, with 'jay' filling the %.

Escaping Literal % and _

Sometimes the text you are searching for genuinely contains a literal percent sign or underscore character, not as a wildcard, but as real data — for example, a discount column containing the text '50%'. To search for the literal character instead of treating it as a wildcard, escape it with a backslash.

SELECT discount_label
FROM promotions
WHERE discount_label LIKE '50\%';

Note

This lesson only briefly introduces escaping — it comes up rarely in beginner-level queries, since most real-world text does not contain literal % or _ characters. Just remember the backslash trick exists if you ever need it.

Common Mistakes

Avoid These Mistakes

Confusing % (any number of characters) with _ (exactly one character) — they are not interchangeable.
Forgetting that % can match zero characters, not just one or more.
Miscounting underscores when matching a fixed-length pattern like '_ohn'.
Searching for a literal % or _ without escaping it, causing MySQL to treat it as a wildcard instead of real text.

Best Practices

Reach for % when the number of surrounding characters is unknown or irrelevant.
Reach for _ only when you know exactly how many characters should appear in that position.
Combine wildcards sparingly — very complex patterns are often better replaced by MySQL's regular expression support, covered later in this course.
Always test a new pattern against a few sample rows before relying on it in an important query.

Frequently Asked Questions

Can I use % and _ in the same pattern?

Yes, they can be combined freely in a single pattern, as shown in the 'S_n%' example in this lesson.

Does % match a completely empty string?

Yes. Since % matches zero or more characters, a pattern like 'A%' also matches a value that is exactly 'A' with nothing following it.

How many _ characters do I need for a longer fixed portion?

One _ per unknown character. For example, '__hn' would require exactly two unknown characters before "hn", not one.

What if I need to match a literal underscore in real data?

Escape it with a backslash, like LIKE 'test\_case', so MySQL treats it as a literal underscore rather than a single-character wildcard.

Key Takeaways

% matches zero or more characters of any kind, anywhere in the pattern.
_ matches exactly one character, no more and no less.
Wildcards can be combined in a single pattern, like 'S_n%'.
A literal % or _ can be matched by escaping it with a backslash.

Summary

The % and _ wildcards give LIKE its real power, letting you describe flexible or fixed-length text patterns instead of only exact matches.

In this lesson, you learned exactly what each wildcard matches, worked through a table of pattern examples, combined both wildcards in one query, and saw how to escape a literal % or _. Next, you will learn how to handle missing data using IS NULL and IS NOT NULL.

Lesson 40 Completed

You understand what % and _ each match.
You can read and write combined wildcard patterns.
You know how to escape a literal % or _ character.
You are ready to learn how MySQL handles missing values.

Next Lesson →