MySQL Wildcards
MySQL Wildcards
Wildcards in MySQL are special characters used with the LIKE operator to search for patterns within string values. They allow flexible and partial matching instead of exact matching.
Wildcards are mainly used in search queries, especially when you don’t know the exact value you’re searching for.
Wildcards Used with LIKE
| Wildcard | Description |
|---|---|
% | Matches zero, one, or many characters |
_ | Matches exactly one character |
[] | Matches any single character within the bracket (used in SQL Server, not commonly in MySQL) |
[^ ] or [! ] | Matches any single character NOT in the bracket (rare in MySQL) |
In MySQL, the most commonly used wildcards are
%and_.
Example Table: products
| id | name | category |
|---|---|---|
| 1 | Apple iPhone | Mobile |
| 2 | Samsung Galaxy | Mobile |
| 3 | Dell Laptop | Laptop |
| 4 | HP Laptop | Laptop |
| 5 | Apple Watch | Watch |
Using % Wildcard
1. Starts With
Matches:
Apple iPhone
Apple Watch
2. Ends With
Matches:
Dell Laptop
HP Laptop
3. Contains
Matches:
Samsung Galaxy
Using _ Wildcard (Single Character Match)
Match a Name with Exactly 5 Characters
(5 underscores → exactly 5 characters)
2nd Character Match
Matches words where 2nd letter is ‘a’, such as:
Samsung Galaxy
Apple Watch (no match here; 2nd letter = p)
Combining % and _
Breakdown:
| Pattern | Meaning |
|---|---|
A | Must start with A |
_ | Any single character |
p% | Followed by p and anything after |
Matches:
Apple iPhone (A p …)
Apple Watch
Escape Characters (When Searching Wildcard Symbols)
If you want to search for actual characters like % or _, use ESCAPE:
This searches for names containing an actual underscore (_), not a wildcard.
Summary Table
| Wildcard | Example | Meaning |
|---|---|---|
% | 'A%' | Starts with A |
% | '%e' | Ends with e |
% | '%top%' | Contains “top” |
_ | 'A_p%' | A + one character + p + rest |
_ _ _ | '___' | Exactly 3 characters |
When to Use Wildcards
| Use Case | Example |
|---|---|
| Searching unknown spelling | %sung% → Samsung |
| Searching partial match | %Laptop |
| Auto-suggest or search box | %keyword% |
