Part 4: Mastering SQL Wildcards | SQL Challenges & Capstone
SQL Filtering & Search Commands Practice · Part 4 · Lecture Notes
Mastering SQL Wildcards
How to build flexible text search patterns using % (any characters) and _ (exactly one character) inside LIKE clauses.
Part 4 of 666% Complete
% Wildcard
_ Wildcard
Pattern Matching
Advanced Search
8 Practice Questions
Learning Objectives
Understand both SQL wildcards: % for any-length sequences and _ for exactly one character
Use % to match values that start with, end with, or contain specific text
Use _ to match values of a specific length or with a known character at a specific position
Combine % and _ in a single LIKE pattern for advanced text searching
Click any section header to open or close it
Wildcards are special characters used with LIKE to perform flexible text searches. They represent unknown parts of a value — either a single character or any number of characters. SQL has two core wildcard symbols, and understanding both gives analysts precise control over text pattern matching.
In industry, analysts use wildcards to search incomplete names, locate product variations, identify matching business records, analyze customer information when full values are unknown, and perform advanced text filtering across large datasets.
The Two SQL Wildcards
% represents zero, one, or many characters — it matches anything of any length. _ represents exactly one character — it matches a single position in the text. Both are always used inside a LIKE pattern.
Basic Wildcard Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE ‘pattern’;
The % wildcard is the most commonly used text search tool in SQL. It matches any sequence of characters — including no characters at all. Wherever % appears in a pattern, the database will accept anything in that position.
% at the Start, End, or Both
Place % at the start to match values ending with your text. Place % at the end to match values starting with your text. Place % on both sides to match your text appearing anywhere inside the value. The position of % communicates which part of the value is known.
Products Containing the Word Kit
SELECT *
FROM products
WHERE product_name LIKE ‘%Kit%’;
Campaigns Containing the Word Social
SELECT *
FROM campaigns
WHERE campaign_name LIKE ‘%Social%’;
The _ wildcard represents exactly one character — no more, no fewer. It is used when the position of an unknown character matters. One underscore = one character slot. Two underscores = two character slots. This makes _ useful for searching names of specific lengths or values with known structures.
Practical Uses of _
Use _ when you know the length of a value or the position of a character. LIKE ‘_va’ matches any three-letter name where the second and third letters are va — such as Ava. LIKE ‘L___’ matches any four-letter name starting with L — such as Liam.
Names Where 2nd and 3rd Letters Are va
SELECT *
FROM users
WHERE first_name LIKE ‘_va’;
Four-Letter Names Starting With L
SELECT *
FROM users
WHERE first_name LIKE ‘L___’;
% and _ can be used together in a single pattern to create highly specific text searches. Each wildcard handles a different type of unknown — % for variable-length unknowns, _ for single-position unknowns. Combining them lets analysts match complex patterns that neither wildcard could handle alone.
Wildcards Only Work Inside LIKE
The % and _ characters are only treated as wildcards when used inside a LIKE clause. Outside of LIKE, % is the modulo operator and _ is a regular underscore character. Never confuse them with WHERE column = ‘%value%’ — that searches for a literal percent sign.
Last Name Following Pattern _arcia
SELECT *
FROM users
WHERE last_name LIKE ‘_arcia’;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve users whose first_name starts with the letter M
AI Prompt
Write a MySQL query to show users whose first_name starts with the letter M.
SQL Answer
SELECT *
FROM users
WHERE first_name LIKE ‘M%’;
Practice 2Retrieve products containing the word Support in the product_name
AI Prompt
Write a MySQL query to show products where product_name contains the word Support.
SQL Answer
SELECT *
FROM products
WHERE product_name LIKE ‘%Support%’;
Practice 3Retrieve campaigns ending with the word Campaign
AI Prompt
Write a MySQL query to show campaigns where campaign_name ends with the word Campaign.
SQL Answer
SELECT *
FROM campaigns
WHERE campaign_name LIKE ‘%Campaign’;
Practice 4Retrieve users whose first_name has exactly 4 letters and starts with L
AI Prompt
Write a MySQL query to show users whose first_name has exactly 4 letters and starts with L.
SQL Answer
SELECT *
FROM users
WHERE first_name LIKE ‘L___’;
Practice 5Retrieve products whose product_name starts with Business
AI Prompt
Write a MySQL query to show products whose product_name starts with Business.
SQL Answer
SELECT *
FROM products
WHERE product_name LIKE ‘Business%’;
Practice 6Retrieve users whose country contains the word United
AI Prompt
Write a MySQL query to show users whose country contains the word United.
SQL Answer
SELECT *
FROM users
WHERE country LIKE ‘%United%’;
Practice 7Retrieve users whose last_name starts with exactly one character followed by arcia
AI Prompt
Write a MySQL query to show users whose last_name follows the pattern _arcia.
SQL Answer
SELECT *
FROM users
WHERE last_name LIKE ‘_arcia’;
Practice 8Retrieve support tickets whose ticket_status starts with P
AI Prompt
Write a MySQL query to show support tickets whose ticket_status starts with the letter P.
SQL Answer
SELECT *
FROM support_tickets
WHERE ticket_status LIKE ‘P%’;
Knowledge Check
Five questions — select the best answer for each.
1. What does the % wildcard represent in a LIKE pattern?
AExactly one character
BZero, one, or many characters
COnly alphabetic characters
DThe end of a text value
Correct. % matches any sequence of characters — including nothing at all. It represents a variable-length unknown in the pattern.
Not quite. % represents zero, one, or many characters — it matches any sequence of any length.
2. What does the _ wildcard represent in a LIKE pattern?
AAny number of characters
BA space character
CExactly one character
DThe start of a text value
Correct. _ represents exactly one character — one underscore = one position. This makes it useful for matching values of a specific length or structure.
Not quite. _ represents exactly one character — one underscore occupies exactly one character position in the pattern.
3. Which LIKE pattern matches four-letter names starting with L?
ALIKE ‘L%’
BLIKE ‘L___’
CLIKE ‘_L__’
DLIKE ‘L_’
Correct. L followed by three underscores = L plus exactly three more characters = four-letter names starting with L. Liam matches; Lucas does not (5 letters).
Not quite. L followed by three underscores matches exactly 4-character values starting with L. Each _ represents exactly one character.
4. Which query finds last names matching the pattern _arcia?
AWHERE last_name LIKE ‘arcia’
BWHERE last_name LIKE ‘%arcia%’
CWHERE last_name LIKE ‘_arcia’
DWHERE last_name LIKE ‘arcia_’
Correct. _arcia means: exactly one unknown character, then arcia. Garcia matches — G is the one character before arcia.
Not quite. _arcia uses _ for exactly one character before arcia. LIKE ‘_arcia’ matches Garcia (G + arcia).
5. Outside of a LIKE clause, what does % mean in SQL?
AIt is a wildcard matching any characters
BIt is the modulo arithmetic operator
CIt represents a NULL value
DIt is a comment marker
Correct. Outside of LIKE, % is the modulo operator — it returns the remainder of a division. Only inside a LIKE pattern does % act as a wildcard.
Not quite. Outside of LIKE, % is the modulo operator. The wildcard meaning only applies inside LIKE patterns.
Part 4 Summary
The key ideas from this lecture — keep these in mind as you move forward.
Wildcards allow analysts to perform flexible and advanced text searches when business information is partially known. % matches any sequence of characters. _ matches exactly one character. Both only work inside LIKE patterns.
