SQL Wildcards

SQL wildcards are special placeholder characters you use together with the LIKE operator to match parts of a text value instead of the whole thing. Instead of writing WHERE name = 'John' and only ever finding rows where the column is exactly 'John', wildcards let you ask for rows where a column starts with, ends with, contains, or fits a specific shape of characters. This turns rigid equality checks into flexible pattern searches, which is essential for real-world tasks like searching names, filtering product codes, or finding email addresses from a particular domain.

Overview: How Wildcards Work

Standard SQL defines two wildcard characters that only have special meaning inside a LIKE (or NOT LIKE) pattern:

• % — matches any sequence of zero or more characters.
• _ — matches exactly one character (any single character).

These characters are not wildcards everywhere in SQL — outside of a LIKE pattern, % is the modulo operator and _ is just a normal character allowed in identifiers. They only trigger pattern matching when they appear inside the string literal (or parameter) on the right-hand side of LIKE.

When the database engine evaluates column LIKE pattern, it walks through the pattern left to right and tries to align it against the column value: literal characters must match exactly (case-insensitively in SQLite and MySQL by default, case-sensitively in PostgreSQL), _ consumes exactly one character wherever it appears, and % consumes any run of characters — including zero — and may require backtracking if a later part of the pattern needs to match further along the string. For example, matching '%son' against 'Jackson' means the engine lets % swallow 'Jack' so that the literal 'son' lines up with the end of the string.

Dialect note: SQL Server (T-SQL) additionally supports bracket wildcards like [abc] (any one character in the list) and [^abc] (any one character not in the list). Standard SQL, SQLite, MySQL, and PostgreSQL’s LIKE do not support bracket character classes — in those engines a bracket in a pattern is treated as a literal character, not a wildcard. SQLite offers its own separate GLOB operator that uses Unix-style wildcards (*, ?, and [abc]) and is always case-sensitive, but that is a different operator from LIKE.

Syntax

SELECT name
FROM employees
WHERE name LIKE 'A%';

The general shape is column LIKE pattern [ESCAPE escape_char], used inside a WHERE (or HAVING) clause:

Part	Meaning
column	The text column (or expression) being tested.
LIKE / NOT LIKE	The operator; NOT LIKE returns rows that do not match the pattern.
pattern	A string literal containing literal characters mixed with % and/or _ wildcards.
ESCAPE escape_char	Optional. Declares a character (e.g. \) that, when placed before % or _ in the pattern, makes it match that character literally.

Wildcard	Matches	Example pattern	Would match
%	Zero or more characters	'A%'	'A', 'Alice', 'Ab123'
_	Exactly one character	'A_'	'A1', 'AB' (not 'A' or 'A10')

Examples

Example 1: Finding names that contain a substring

SELECT customer_name, country
FROM customers
WHERE customer_name LIKE '%o%'
ORDER BY customer_name;

Result:

customer_name	country
Acme Corp	USA
Globex	UK

The pattern '%o%' means “any characters, then a literal o, then any characters” — in other words, “contains an o anywhere.” Acme Corp matches because of the o in “Corp,” and Globex matches because of the o after the Gl. Initech is excluded because it has no o at all. Wrapping a pattern in % on both sides is the standard way to write a “contains” search in SQL.

Example 2: Matching a suffix

SELECT product_name, price
FROM products
WHERE product_name LIKE '%dget'
ORDER BY product_name;

Result:

product_name	price
Gadget	19.99
Widget	9.99

Here the % only appears at the front, so the pattern means “any characters, then literally ends with dget.” Both Gadget and Widget end in those four letters, so both are returned; Gizmo does not end in dget and is excluded. Placing % only at the end ('dget%') would instead search for names that start with “dget,” and using it on both sides ('%dget%') would match anything containing “dget” anywhere.

Example 3: Matching a fixed number of characters with _

CREATE TABLE parts (
    id INTEGER PRIMARY KEY,
    code TEXT,
    description TEXT
);

INSERT INTO parts (code, description) VALUES
    ('A1', 'Widget'),
    ('A2', 'Gadget'),
    ('B1', 'Sprocket'),
    ('AB', 'Gizmo'),
    ('A10', 'MegaWidget');

SELECT code, description
FROM parts
WHERE code LIKE 'A_'
ORDER BY code;

Result:

code	description
A1	Widget
A2	Gadget
AB	Gizmo

The pattern 'A_' is exactly two characters long: a literal A followed by exactly one wildcard character. A1, A2, and AB all qualify because they are two-character codes starting with A. B1 fails because it doesn’t start with A, and — importantly — A10 fails too, because it has three characters and _ only ever stands for one. This is the key difference from %: _ fixes the length at that position instead of allowing any length.

Example 4: Escaping a literal wildcard character

CREATE TABLE discounts (
    id INTEGER PRIMARY KEY,
    code TEXT,
    label TEXT
);

INSERT INTO discounts (code, label) VALUES
    ('SALE10%', 'Ten percent off'),
    ('SALE20', 'Twenty flat'),
    ('SALE_5', 'Five dollars off');

SELECT code, label
FROM discounts
WHERE code LIKE '%\%' ESCAPE '\'
ORDER BY code;

Result:

code	label
SALE10%	Ten percent off

Sometimes the data itself contains a literal % or _ that you want to search for as a plain character, not as a wildcard. The ESCAPE '\' clause declares \ as an escape character, so \% in the pattern means “a literal percent sign” instead of “any sequence.” The pattern '%\%' reads as “any characters, followed by a literal percent sign at the end,” which only SALE10% satisfies — SALE20 has no % at all, and SALE_5 has a literal underscore but no percent sign.

How It Works Step by Step (Under the Hood)

A query with LIKE in its WHERE clause is processed in the usual logical order that SQL applies to every query:

1. FROM — the source table(s) are identified.
2. WHERE — every candidate row is tested against the condition, including any LIKE comparisons; only rows where the pattern matches survive.
3. GROUP BY / HAVING — if present, remaining rows are grouped and further filtered (a LIKE can also appear in HAVING, evaluated after grouping).
4. SELECT — the requested columns/expressions are computed for the surviving rows.
5. ORDER BY — results are sorted.
6. LIMIT — the row count is capped, if specified.

Inside the WHERE step, the engine’s pattern-matching algorithm scans the pattern and the column value together. A leading literal (like 'John%') lets the engine confirm a mismatch the moment the first few characters diverge. A % in the middle of a pattern (like '%dget' or '%o%') forces the engine to try multiple alignments — conceptually, “does the rest of the pattern match if I skip 0 characters here? 1 character? 2?” — until it finds a fit or exhausts the string.

This has a real performance consequence: a pattern that starts with a literal prefix, such as 'John%', can use a B-tree index on that column the same way a range condition can (the engine can jump straight to the “John” region of the index). A pattern that starts with %, such as '%son' or '%o%', cannot be seeked into an ordinary index at all — there is no fixed prefix to jump to, so the engine must scan every row and test the pattern against each one. If you frequently need “contains” or “ends with” searches on a large table, a dedicated full-text search index (SQLite’s FTS5, PostgreSQL’s pg_trgm/GIN index, or MySQL’s FULLTEXT index) is the appropriate tool, not a leading-wildcard LIKE.

Case sensitivity is also engine-specific and worth knowing precisely: SQLite’s built-in LIKE is case-insensitive for ASCII letters by default (so 'john' LIKE 'John' is true), MySQL’s default depends on the column’s collation (usually case-insensitive), and PostgreSQL’s LIKE is always case-sensitive (use ILIKE there for case-insensitive matching, or wrap both sides in LOWER() for portability).

Common Mistakes

Mistake 1: Forgetting the wildcard entirely

CREATE TABLE staff2 (
    id INTEGER PRIMARY KEY,
    name TEXT
);

INSERT INTO staff2 (name) VALUES
    ('John Smith'),
    ('John Smith Jr'),
    ('Johnny Appleseed');

-- Intended: find everyone whose name starts with 'John'
SELECT name FROM staff2 WHERE name LIKE 'John';

Result: no rows at all.

Without a % or _, a LIKE pattern behaves exactly like = — it only matches values equal to the literal text. None of the three names is exactly 'John', so nothing comes back, even though every row is clearly “John-ish.” The fix is to add a wildcard where partial matching is intended:

CREATE TABLE staff2 (
    id INTEGER PRIMARY KEY,
    name TEXT
);

INSERT INTO staff2 (name) VALUES
    ('John Smith'),
    ('John Smith Jr'),
    ('Johnny Appleseed');

SELECT name FROM staff2 WHERE name LIKE 'John%'
ORDER BY name;

Result: John Smith, John Smith Jr, Johnny Appleseed — all three, because each one starts with the four literal characters John.

Mistake 2: Forgetting to quote the pattern

SELECT name FROM employees WHERE name LIKE J%;

This fails to run at all. Because the pattern isn’t wrapped in quotes, the parser doesn’t see a string literal — it tries to read J as a column or table reference and % as the modulo operator, which is not valid here and raises an error (something like no such column: J). A LIKE pattern must always be a quoted string (or a bound parameter), never a bare identifier.

Other pitfalls to watch for

• Assuming case sensitivity is the same everywhere — it isn’t (see the “Under the Hood” section above); test your specific engine rather than assuming.
• Writing LIKE '[A-C]%' expecting a SQL Server-style character class — in SQLite, MySQL, PostgreSQL, and standard SQL, the brackets are treated as literal characters, so this silently matches strings that literally start with [, not “A, B, or C.” It won’t error, it will just quietly return the wrong rows.
• Putting an untrusted, user-supplied string directly into a pattern without escaping it — if a user’s search text happens to contain % or _, it will be treated as a wildcard, producing surprising matches (or, if concatenated into raw SQL instead of bound as a parameter, a SQL injection risk).

Best Practices

• Use % for “any length” matches and _ only when you truly need to fix the number of characters at a position.
• Prefer a leading literal ('abc%') over a leading wildcard ('%abc') whenever possible — it lets the query planner use an index instead of scanning every row.
• For heavy “contains” searching on large tables, reach for a full-text search index (FTS5, pg_trgm, FULLTEXT) instead of relying on '%text%' at scale.
• Always bind user input as a query parameter rather than concatenating it into the pattern string, both to avoid SQL injection and to keep special characters like % from being misinterpreted.
• Escape literal % or _ characters in your pattern with ESCAPE whenever the data you’re searching might legitimately contain them.
• Don’t rely on bracket character classes ([abc]) unless you know you’re specifically targeting SQL Server — they silently do nothing useful on SQLite, MySQL, or PostgreSQL.
• Check case sensitivity for your specific database before assuming a pattern will (or won’t) match different letter cases.

Practice Exercises

1. Using the employees table (id, name, department, salary, hire_date), write a query that returns every employee whose department ends with the letters ing (this should match both Engineering and Marketing).
2. Using the customers table (customer_id, customer_name, country), write a query that returns customers whose customer_name is exactly six characters long, using _ wildcards, and check which of the sample customers qualifies.
3. Create a small table of your own with a text column containing a few values that include a literal underscore (for example, product codes like 'SKU_100' and 'SKU200'). Write a query using LIKE with an ESCAPE clause that finds only the codes containing a literal underscore.

Summary

• LIKE enables pattern matching in WHERE/HAVING; % matches zero or more characters, _ matches exactly one.
• Wildcards only have special meaning inside a LIKE/NOT LIKE pattern, not elsewhere in SQL.
• Leading-% patterns can’t use a standard index and force a full scan; trailing-% patterns can.
• Use ESCAPE to search for a literal % or _ in the data.
• Bracket character classes ([abc]) are a SQL Server extension, not standard LIKE behavior — they don’t work as wildcards in SQLite, MySQL, or PostgreSQL.
• Case sensitivity of LIKE differs by database — verify your engine’s default rather than assuming.