SQL wildcards are an essential skill for data analysts and developers. This guide will walk you through everything from basics to advanced tips—using simple terms, examples, and visual explanations.
SQL wildcards are used in the LIKE clause to search for data based on patterns, not exact text. They’re commonly used in SELECT queries to filter results dynamically.
| Wildcard | Usage | Example |
|---|---|---|
| % | Zero or more characters | ‘a%’ → apple, ant, alarm |
| _ | Single character | ‘a_’ → an, at, ab |
| [abc] | Matches a, b, or c | ‘[ae]%’ → apple, elephant |
| [^abc] | Not a, b, or c | ‘[^a]%’ → excludes ‘apple’ |
To match any number of characters, use %.
SELECT * FROM employees
WHERE first_name LIKE 'J%';
The underscore _ matches exactly one character.
SELECT * FROM users
WHERE username LIKE '_a%';
You can combine wildcards for more refined pattern matching:
SELECT * FROM students
WHERE name LIKE 'An__%';
Queries starting with '%' (e.g. '%apple') are slower because indexes can’t be used effectively. Use prefix matching if possible.
% = multiple characters; _ = one character.LIKE in WHERE clauses.Want more? In the next part, we’ll explore advanced wild operators like IN, BETWEEN, and wildcards in real-world filtering!
In this section, we’ll explore SQL wild operators—powerful tools that help you write smarter queries by matching conditions, sets, and ranges.
IN OperatorThe IN operator allows you to filter results based on a list of values.
SELECT * FROM employees
WHERE department IN ('HR', 'Sales', 'Marketing');
It’s cleaner and more efficient than writing:
WHERE department = 'HR' OR department = 'Sales' OR department = 'Marketing'
NOT INThis operator excludes results from a given list.
SELECT * FROM orders
WHERE status NOT IN ('Canceled', 'Returned');
BETWEENUse BETWEEN to filter values within a specified range.
SELECT * FROM products
WHERE price BETWEEN 100 AND 300;
BETWEEN operator is inclusive. This means it includes both 100 and 300.
IS NULL and IS NOT NULLThese are used to filter NULL (missing) values.
SELECT * FROM customers
WHERE email IS NULL;
Combine wildcards and operators for powerful filtering:
SELECT * FROM products
WHERE category IN ('Electronics', 'Accessories')
AND name LIKE 'A%';
Use indexes with operators like IN and BETWEEN for faster queries. Avoid starting LIKE with % when performance is critical.
IN filters values from a list.NOT IN excludes values from a list.BETWEEN checks if values fall in a range.IS NULL finds missing or undefined values.LIKE for advanced search.Coming up in Part 3: real-world applications, combining wildcards & operators in search filters, and best practices for clean, readable SQL.
Now that you understand the mechanics of SQL wildcards and operators, let’s apply them to real-world problems like search filtering, dashboards, and data validation.
Many websites allow users to search by partial names or emails. You can build a backend SQL query like:
SELECT * FROM users
WHERE email LIKE '%@gmail.com'
AND status = 'active';
Combine IN with LIKE for multi-category reporting.
SELECT COUNT(*), category
FROM products
WHERE category IN ('Electronics', 'Books')
AND name LIKE '%Pro%'
GROUP BY category;
Use IS NULL and LIKE to identify bad records during audits.
SELECT * FROM customers
WHERE phone IS NULL
OR email NOT LIKE '%@%.%';
If your search includes a character like % or _, you must escape it:
SELECT * FROM documents
WHERE title LIKE '%95\%%' ESCAPE '\';
% for better performance.IN for multiple OR conditions for clean code.LIKE with IS NULL or BETWEEN for custom filters.SQL wildcards (% and _) and wild operators (IN, BETWEEN, IS NULL) are tools every SQL developer must master. Whether you’re building search bars, dashboards, or data validation scripts—these tools help you extract smarter insights from your database with ease.
Bookmark this guide and share it with your team!