The previous lesson used % without fully explaining it. This lesson covers both standard SQL wildcards in depth, %, matching any number of characters, and _, matching exactly one.
Learning Objectives
- Use % to match any number of characters, including zero.
- Use _ to match exactly one character.
- Match a literal % or _ character using ESCAPE.
The % Wildcard
% stands in for any sequence of characters, including an empty one, 'H%' matches 'H' by itself just as much as 'Hopper'.
| Pattern | Matches |
|---|---|
| 'H%' | Starts with H (Hopper, Hamilton, H) |
| '%son' | Ends with son (Jackson, Johnson) |
| '%an%' | Contains "an" anywhere in the text (Vaughan) |
SELECT last_name FROM employees WHERE last_name LIKE '%a%';
The _ Wildcard
_ matches exactly one character, no more, no less. '_opper' matches 'Hopper' (H is the one character) but not 'Topperx' or 'opper' (wrong length).
SELECT last_name FROM employees WHERE last_name LIKE '_opper';
Combining Wildcards
'H_%' means: starts with H, followed by at least one more character. This excludes a last name that's just 'H' alone (the _ demands at least one character after it), unlike plain 'H%'.
SELECT last_name FROM employees WHERE last_name LIKE 'H_%';
Matching a Literal % or _
If the text you're actually searching for contains a real % or _ character, ESCAPE declares a character to treat as an escape marker, so the wildcard right after it is read literally instead of as a wildcard. The backslash is a common choice, but any single character works.
SELECT code FROM discount_codes WHERE code LIKE '50\%' ESCAPE '\'; -- Matches the literal text "50%", not "any text starting with 50"
SQL Server additionally supports square-bracket wildcards, [abc] matches any single character among a, b, or c, and [^abc] matches any single character not among them. This is a SQL Server-specific extension, not standard SQL, and isn't portable to the other five databases in this course.
Try It Yourself
Write a query matching last names that are exactly 6 characters long and start with H.
SELECT last_name FROM employees WHERE last_name LIKE 'H_____'; -- H followed by exactly 5 more characters (_ repeated 5 times) = 6 total
Frequently Asked Questions
Can I use multiple % and _ in one pattern?
Yes, as many as needed, in any combination and order, '_a%son' is valid: second character is 'a', ends in 'son'.
Does ESCAPE require a specific character, like backslash?
No, ESCAPE accepts any single character you choose, as long as you then use that exact character before the wildcard you want treated literally. Backslash is just a common convention, not a requirement.
Common Beginner Mistakes
Forgetting that % matches zero characters, not "one or more"
'H%' matches the single-character string 'H' by itself, not just longer strings starting with H. If "at least one character after H" is the actual intent, use 'H_%' instead.
Searching for a literal % or _ without using ESCAPE
WHERE code LIKE '50%' matches any text starting with '50', not the literal text '50%'. Without ESCAPE, there's no way to tell SQL you mean the character itself rather than the wildcard.
Best Practices
- Reach for _ when a pattern has an exact, known length, and % when it doesn't.
- Always use ESCAPE when searching for text that might contain a literal % or _, don't assume user input is wildcard-free.
- Avoid SQL Server's [ ] bracket wildcards in code meant to run on more than one database, they aren't portable.
Interview Questions
What's the difference between 'H%' and 'H_%' as LIKE patterns?
'H%' matches any text starting with H, including just 'H' alone, since % can match zero characters. 'H_%' requires at least one character after the H, since _ specifically demands exactly one character, excluding a bare 'H' match.
How would you search for a product code containing a literal underscore, like 'SKU_100'?
WHERE code LIKE '%SKU\_100%' ESCAPE '\'; — without ESCAPE, the underscore in SKU_100 would be interpreted as the wildcard (matching any one character) rather than a literal underscore.
Summary
This lesson covered % (any number of characters) and _ (exactly one character) in depth, combining them in one pattern, and using ESCAPE to match a literal wildcard character.
What's Next?
The next lesson covers IN, matching a value against a list of options.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.