SQL Wildcards
SQL wildcards are special placeholder characters used alongside the LIKE operator to search for complex patterns within text columns.
They allow you to build flexible queries when you only know a fraction of the exact string value you are looking for.
While standard SQL defines a universal set of wildcards, minor layout syntax differences exist depending on whether you are querying inside MySQL/PostgreSQL or MS SQL Server.
The Standard ANSI SQL Wildcards
These characters work across virtually all major relational databases, including MySQL, PostgreSQL, SQLite, and Oracle.
| Wildcard | What it Matches | Real-World Example | Matches Found |
|---|---|---|---|
| % | Zero, one, or multiple characters | WHERE name LIKE 'Ch%' | Chris, Charlotte, Che |
| % | (Anywhere in the text) | WHERE email LIKE '%@gmail.com' | john**@gmail.com**, dev**@gmail.com** |
| _ | Exactly one single character | WHERE room LIKE 'B_01' | B101, B201, BA01 |
MS SQL Server Exclusive Wildcards
If you are working with Microsoft SQL Server (T-SQL), you have access to advanced regular-expression style wildcards for even tighter string mapping.
| Wildcard | What it Matches | Real-World Example | Matches Found |
|---|---|---|---|
| [charlist] | Any single character inside the brackets | WHERE code LIKE '[A-C]99' | A99, B99, C99 (Skips D99) |
| [^charlist] or [!charlist] | Any single character NOT inside the brackets | WHERE code LIKE '[^A-C]99' | D99, E99 (Skips A99, B99) |
Syntax Blueprints
Combining Multiple Wildcards
You can chain multiple wildcards together to pinpoint text with absolute structural rules.
-- Finds employees whose name starts with 'J', has any characters,
-- then contains an 'n', followed by exactly two characters at the end.
SELECT first_name
FROM employees
WHERE first_name LIKE 'J%n__'; -- Matches 'Jenson', 'Jordan'
How to Search for Literal Wildcard Characters (ESCAPE)
If your actual data text contains a literal % or _ sign (e.g., searching for product rows containing the text "50%"), you must use an escape character so SQL doesn't confuse it for a wildcard.
-- The backslash tells SQL to treat the following '%' as normal text, not a placeholder
SELECT product_name, discount_rate
FROM inventory
WHERE discount_rate LIKE '50\%' ESCAPE '\';
Code Performance Warning
Using a wildcard at the beginning of a search string (like LIKE '%search') makes it impossible for database engines to use your standard column indexes.
This forces a highly inefficient Full Table Scan, which will dramatically lag your production environment if the table scales into millions of rows.