SQL Wildcard Characters Explained

SQL Tutorial · Chapter 10 of 32

SQL wildcards are special characters used with the LIKE operator to match part of a text value instead of the whole thing. The two standard wildcards are % (zero or more characters) and _ (exactly one character). SQL Server adds [ ] character ranges, and every database provides an ESCAPE option so you can search for a literal % or _.

Table of Contents

SQL Wildcard Characters

Wildcard	Meaning	Supported in	Example
%	Any string of zero or more characters	All databases	‘S%’ matches Sydney, Singapore
_	Exactly one character	All databases	‘L_ndon’ matches London
[abc]	One character from the set	SQL Server, MS Access	‘[MS]%’ matches Mumbai, Madrid, Sydney, Singapore
[a-f]	One character in the range	SQL Server, MS Access	‘[A-M]%’ matches names A to M
[^abc] or [!abc]	One character not in the set	SQL Server / Access	‘[^M]%’ excludes Mumbai, Madrid

Syntax

SELECT columns FROM table_name WHERE column LIKE 'pattern';
SELECT columns FROM table_name WHERE column LIKE 'pattern' ESCAPE '\';

Sample Table

Customers
CustomerID	Name	City	Country
1	Anita Sharma	Mumbai	India
2	John Smith	London	UK
3	Maria Garcia	Madrid	Spain
4	Wei Chen	Singapore	Singapore
5	Emma Brown	Sydney	Australia

Example 1: The % Wildcard

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

Name	City
Wei Chen	Singapore
Emma Brown	Sydney

Because % also matches zero characters, 'Sydney%' matches Sydney itself.

Example 2: The _ Wildcard

SELECT City
FROM Customers
WHERE City LIKE 'M_____';

City
Mumbai
Madrid

Both cities are exactly six letters beginning with M. Each underscore must be matched by one character, no more and no fewer.

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%';

Name
John Smith

Example 4: Character Sets in SQL Server

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

Name	Country
Anita Sharma	India
Maria Garcia	Spain
Wei Chen	Singapore

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, ~ '^[IS]' in PostgreSQL).

Example 5: Escaping a Literal Wildcard

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

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

Notes
Discount 10%

The backslash tells the database that the following % is an ordinary character. In SQL Server you can also write '%10[%]%'.

Works in MySQL, SQL Server, PostgreSQL, SQLite

% and _ work everywhere. Only SQL Server and Access understand [ ] sets.
MySQL treats backslash as the default escape character even without ESCAPE; the others need the ESCAPE clause.
PostgreSQL LIKE is case sensitive; use ILIKE. Others ignore case for ASCII letters by default.
For complex patterns, MySQL has REGEXP, PostgreSQL has ~ and SIMILAR TO, SQLite has GLOB (with * and ?), SQL Server relies on LIKE sets.

Wildcards and Performance

A pattern that begins with literal characters, such as 'S%', can use an index because the database knows where matching values start. A pattern that begins with a wildcard, such as '%i', cannot, and forces a scan of every row. When you need fast searches for text in the middle of a value, use a full-text index rather than a leading wildcard.

Common Mistakes

Using [ ] in MySQL or PostgreSQL, where the brackets are matched literally.
Trailing spaces in CHAR columns: ‘UK’ stored in CHAR(5) is ‘UK ‘, so LIKE 'UK' may fail while LIKE 'UK%' works.
Searching for an underscore without escaping it, so it matches any character.

Practice Exercise

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

Show answers

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

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

Related Chapters

SQL LIKE – the operator that uses wildcards
SQL WHERE – filtering rows
SQL AND, OR, NOT – combine several patterns
SQL from Scratch: full course overview

FAQ

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?

Escape it: WHERE Code LIKE ‘A\_%’ ESCAPE ‘\’ matches codes starting with A followed by a real underscore.

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