SQL Wildcards: %, _, [ ] and Escape Characters Explained

SQL Tutorial · Chapter 10 of 32

SQL wildcards are special characters you use with the LIKE operator to match part of a text value instead of the whole thing. The two standard wildcards are %, which matches zero or more characters, and _, which matches exactly one character. SQL Server and MS Access also support [ ] character sets, and every major database lets you escape a wildcard when you need to search for a literal % or _.

This chapter covers each wildcard with a small sample table, the exact query, and the rows it returns. You will also see how the rules differ between MySQL, SQL Server, PostgreSQL and SQLite, how wildcards affect performance, and the mistakes that most often return the wrong rows. It is written for beginners who already know SELECT and WHERE.

SQL Wildcard Characters at a Glance

Syntax

SELECT columns
FROM table_name
WHERE column LIKE 'pattern';

-- Search for a literal wildcard character
SELECT columns
FROM table_name
WHERE column LIKE 'pattern' ESCAPE '\';

Wildcards only have special meaning inside a LIKE (or NOT LIKE) pattern. With =, a % or _ is treated as an ordinary character.

Sample Table

Example 1: The % Wildcard

Find customers whose city starts with S.

SELECT Name, City
FROM Customers
WHERE City LIKE 'S%';

Because % also matches zero characters, 'Sydney%' matches Sydney itself. The position of % changes the meaning: 'S%' means starts with S, '%a' means ends with a, and '%an%' means contains “an” anywhere.

Example 2: The _ Wildcard

Find six-letter cities that start with M.

SELECT City
FROM Customers
WHERE City LIKE 'M_____';

The pattern is M followed by five underscores. Each underscore must be matched by exactly one character, no more and no fewer, so a seven-letter city starting with M would not match.

Example 3: Combining % and _

Find names whose second letter is “o”: one character, then o, then anything.

SELECT Name
FROM Customers
WHERE Name LIKE '_o%';

Example 4: Character Sets in SQL Server

Find customers whose country starts with I or S.

SELECT Name, Country
FROM Customers
WHERE Country LIKE '[IS]%';

In MySQL, PostgreSQL and SQLite, write the same filter as Country LIKE 'I%' OR Country LIKE 'S%', or use a regular expression: REGEXP '^[IS]' in MySQL or ~ '^[IS]' in PostgreSQL. SQL Server 2025 and Azure SQL Database also add REGEXP_LIKE(Country, '^[IS]').

Example 5: Excluding Matches with NOT LIKE

List customers whose city does not start with M.

SELECT Name, City
FROM Customers
WHERE City NOT LIKE 'M%';

Remember that NOT LIKE never matches NULL values. If the column can be empty, add OR City IS NULL when you want those rows too.

Example 6: Escaping a Literal Wildcard

Suppose a Promotions table has a Notes column containing the text “Discount 10%”. To find rows with a real percent sign, escape it.

SELECT Notes
FROM Promotions
WHERE Notes LIKE '%10\%%' ESCAPE '\';

The backslash tells the database that the % right after it is an ordinary character; the first and last % are still wildcards. This form works as written in SQL Server, PostgreSQL and SQLite. In MySQL the backslash is already the default escape character and is itself special inside string literals, so drop the ESCAPE clause (LIKE '%10\%%') or write ESCAPE '\\'. In SQL Server you can also wrap the character in brackets: '%10[%]%'. The same approach works for underscores, for example LIKE 'A\_%' ESCAPE '\' finds codes that start with “A_”.

Differences Between MySQL, SQL Server, PostgreSQL and SQLite

Wildcards and Performance

A pattern that begins with literal characters, such as 'S%', can use an index on the column because the database knows where matching values start. A pattern that begins with a wildcard, such as '%i' or '%an%', cannot, and forces the database to check every row. On a few thousand rows you will not notice; on millions you will. When you need fast “contains” searches on large text columns, use a full-text index (FULLTEXT in MySQL, full-text search in SQL Server, or tsvector in PostgreSQL) rather than a leading wildcard.

Common Mistakes and How to Fix Them

• Using [ ] in MySQL or PostgreSQL. The brackets are matched literally, so the query returns nothing. Fix: use OR conditions or a regular expression.
• Using * and ? instead of % and _. Those are Access, SQLite GLOB and spreadsheet wildcards. Fix: use % and _ with LIKE.
• Trailing spaces in CHAR columns. ‘UK’ stored in CHAR(5) can be ‘UK ‘, so LIKE 'UK' may fail. Fix: use LIKE 'UK%' or trim the value.
• Forgetting that _ is a wildcard. LIKE 'A_1' also matches “AB1”. Fix: escape it as shown in Example 6.
• Running UPDATE or DELETE with a broad pattern. DELETE ... WHERE Name LIKE '%a%' removes far more than you expect. Fix: run the same WHERE with SELECT first and check the rows.

Practice Exercise

1. Write a pattern that matches cities ending in “i”.
2. Write a pattern that matches names with exactly four letters before the space (for example “John Smith” has four, “Anita Sharma” has five).

Answers

-- 1
SELECT City FROM Customers WHERE City LIKE '%i';
-- Result: Mumbai

-- 2
SELECT Name FROM Customers WHERE Name LIKE '____ %';
-- Result: John Smith, Emma Brown

Related SQL Chapters

Wildcards only work inside the SQL LIKE operator, which you write in a WHERE clause. To combine several patterns, see SQL AND, OR and NOT; for an exact list of values, the IN operator is simpler than many LIKE conditions. Keep the SQL reference guide handy, and test yourself with the SQL quiz.

Working in spreadsheets as well? Excel and Google Sheets use * and ? as wildcards in functions such as COUNTIF and XLOOKUP. The Excel AI Mastery (100 Formulas) course on NextGenTemplates Academy covers those lookup and counting formulas if you want the spreadsheet side of pattern matching.

Frequently Asked Questions

What are the two main wildcards in SQL?

% matches any sequence of zero or more characters and _ matches exactly one character. Both are used only with the LIKE or NOT LIKE operator.

Can I use * as a wildcard in SQL?

Not with LIKE in standard SQL. * is used in SELECT * to mean all columns. MS Access and SQLite GLOB use * and ?, but standard LIKE uses % and _.

How do I match a literal underscore or percent sign?

Escape it with the ESCAPE clause, for example WHERE Code LIKE 'A\_%' ESCAPE '\' (in MySQL simply LIKE 'A\_%', because backslash is the default escape). In SQL Server you can also write 'A[_]%'.

Is SQL LIKE case sensitive?

It depends on the database. PostgreSQL LIKE is case sensitive (use ILIKE to ignore case). MySQL and SQL Server follow the column’s collation, which is usually case insensitive. SQLite ignores case for ASCII letters.

Why is my LIKE ‘%text%’ query slow?

A leading % stops the database from using a normal index, so it scans every row. Use a pattern that starts with fixed characters where possible, or a full-text index for “contains” searches.

Wrapping Up

Most real-world pattern matching needs only two characters: % for “anything” and _ for “exactly one”. Add [ ] sets when you work in SQL Server, escape wildcards when you search for them literally, and avoid leading % on large tables. Next, practise these patterns on the SQL LIKE chapter’s examples before moving on.

📅 Last updated: October 2026

Chapter 10 of 32 · SQL from Scratch: all 32 chapters