% and _. In this chapter, we dive deeper into SQL wildcard characters — the special characters used with LIKE to build powerful search patterns. You will learn advanced combinations, escaping special characters, and database-specific wildcards.SQL Wildcard Characters
Wildcards are special characters that represent one or more unknown characters in a pattern. They are always used with the LIKE operator inside a WHERE clause.
| Wildcard | Description | Supported In |
|---|---|---|
% |
Zero, one, or multiple characters | All databases |
_ |
Exactly one single character | All databases |
[] |
Any single character in the brackets | SQL Server, MS Access |
[^] or [!] |
Any character NOT in the brackets | SQL Server, MS Access |
We will use this Employees table:
+----+-----------+------------+--------+-----------+
| ID | Name | Department | Salary | City |
+----+-----------+------------+--------+-----------+
| 1 | Priya K. | Sales | 55000 | Mumbai |
| 2 | John D. | Marketing | 62000 | London |
| 3 | Arun M. | IT | 78000 | Delhi |
| 4 | Emma W. | Sales | 51000 | Sydney |
| 5 | Ravi S. | IT | 72000 | Mumbai |
| 6 | Sara L. | HR | 58000 | New York |
+----+-----------+------------+--------+-----------+The % Wildcard — Multiple Characters
The % wildcard matches any sequence of characters, including an empty string (zero characters).
Cities that contain “um”:
SELECT Name, City FROM Employees
WHERE City LIKE '%um%';+-----------+--------+ | Name | City | +-----------+--------+ | Priya K. | Mumbai | | Ravi S. | Mumbai | +-----------+--------+
Departments ending in “s”:
SELECT Name, Department FROM Employees
WHERE Department LIKE '%s';+-----------+------------+ | Name | Department | +-----------+------------+ | Priya K. | Sales | | Emma W. | Sales | +-----------+------------+
The _ Wildcard — Single Character
The _ wildcard matches exactly one character. Use multiple underscores for multiple single characters.
Names where the second character is ‘a’:
SELECT Name FROM Employees
WHERE Name LIKE '_a%';+-----------+ | Name | +-----------+ | Sara L. | | Ravi S. | +-----------+
Cities with exactly 5 characters:
SELECT Name, City FROM Employees
WHERE City LIKE '_____';+-----------+--------+ | Name | City | +-----------+--------+ | Arun M. | Delhi | +-----------+--------+
Combining % and _ Wildcards
You can mix both wildcards in a single pattern for precise matching:
SELECT Name FROM Employees
WHERE Name LIKE '_r%';+-----------+ | Name | +-----------+ | Priya K. | | Arun M. | +-----------+
Common Wildcard Patterns
| Pattern | Meaning |
|---|---|
'A%' |
Starts with A |
'%a' |
Ends with a |
'%or%' |
Contains “or” anywhere |
'_a%' |
Second character is a |
'A_%' |
Starts with A, at least 2 characters |
'A__%' |
Starts with A, at least 3 characters |
'_____' |
Exactly 5 characters |
'%a_' |
Has “a” as the second-to-last character |
⚠️ Performance Warning
Patterns starting with % (like '%son') cannot use indexes and may cause full table scans on large datasets. Patterns starting with a fixed prefix (like 'Jo%') are much faster because the database can use indexes.
Escaping Wildcards
If you need to search for a literal % or _ character in your data, use the ESCAPE clause:
SELECT * FROM Products
WHERE Discount LIKE '%10\%%' ESCAPE '\';The backslash tells the database to treat the next % as a literal character, not a wildcard.
For a complete reference of pattern matching syntax, see the official MySQL pattern matching documentation. In the next chapter, you will learn the SQL IN operator for filtering by a list of values.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a query that finds employees whose name has exactly 7 characters (including the space and initial like “Priya K.”).
SELECT Name FROM Employees
WHERE Name LIKE '_______';📝 What You Learned in This Chapter
%matches zero, one, or many characters_matches exactly one character[]matches any character in the set (SQL Server only)- Combine
%and_for advanced pattern matching - Patterns starting with
%are slower on large tables - Use ESCAPE to search for literal wildcard characters
Frequently Asked Questions
% (matches any number of characters) and _ (matches exactly one character). They let you search for partial matches instead of exact values.% matches any number of characters (including zero). _ matches exactly one character. For example, 'A%' matches A, Ab, Abc, while 'A_' only matches exactly two-character strings starting with A like Ab or Ac.% and _ wildcards work in all SQL databases. The [] bracket wildcard is specific to SQL Server and MS Access. MySQL and PostgreSQL do not support bracket wildcards but offer REGEXP for more advanced pattern matching.WHERE col LIKE '%10\%%' ESCAPE '\' searches for the literal text “10%”. The backslash before % tells the database to treat it as a regular character.ILIKE in PostgreSQL or convert to lowercase with LOWER() for case-insensitive searches.📖 Chapter 16 of 30 — SQL Tutorial on Neotech Navigators
