SQL Wildcard Characters: LIKE Patterns, ESCAPE Rules, and Examples
For SQL beginners who know SELECT and WHERE and want to know what each wildcard in a LIKE pattern matches.
On this page
A wildcard is a character in a LIKE pattern that stands for the characters you don't know. For example, the pattern 'Ali%' finds every name that starts with Ali.
Every pattern sits in the same place of a query:
SELECT columns
FROM table_name
WHERE column_name LIKE 'pattern' ESCAPE 'character';
column_nameis the text column to search.'pattern'has to match the whole value, from its first character to its last. A letter in the pattern matches only that letter.%in the pattern stands for any number of characters, including none._stands for exactly one character.[ ]stands for one character from a set or a range, and[^ ]for one character outside it. Only SQL Server's LIKE has these two.ESCAPE 'character'is optional. It names a character that turns the%or_after it into an ordinary character.
The SQL LIKE operator tutorial covers the rest of LIKE: NOT LIKE and NULL, upper and lower case, and indexes.
Create a sample table of four students:
CREATE TABLE Students (
Name VARCHAR(50) PRIMARY KEY,
Username VARCHAR(50)
);
INSERT INTO Students VALUES
('Alice', 'alice_k'),
('Alina', 'alina'),
('Bob', 'bob_99'),
('Charlie', 'charlie');
The queries with % and _ returned the same rows on PostgreSQL 18.6, MySQL 9.1 and SQLite 3.50.
LIKE reads the pattern against each value from left to right. In 'A_i%', the A and the i have to match exactly, _ takes one character, and % takes the rest:
The % wildcard
% stands for any number of characters, including none. For example, 'Ali%' finds the names that start with Ali.
SELECT Name FROM Students
WHERE Name LIKE 'Ali%'
ORDER BY Name;
| Name |
|---|
| Alice |
| Alina |
Alice and Alina start with Ali, and % takes the rest of each name. Put the % in front, as in '%na', and the query returns only Alina. With a % at both ends, '%li%' finds li anywhere in the name: Alice, Alina and Charlie.
Practice this: Lesson 3 of the SQL tutorial runs WHERE, AND, OR, IN, BETWEEN and LIKE on a shop database in your browser, with checked exercises.
The _ wildcard
_ stands for exactly one character. For example, '_ob' finds the three-letter names that end in ob.
SELECT Name FROM Students
WHERE Name LIKE '_ob'
ORDER BY Name;
| Name |
|---|
| Bob |
The _ takes the B of Bob, and the o and the b match themselves. One _ is one character, never zero and never two, so '_ob' would miss a name like Bobby. Three underscores, '___', find the names of exactly three characters: here, only Bob.
[ ] and [^] in SQL Server
[ ] stands for one character from a set or a range, and [^ ] for one character outside it. For example, '[A-B]%' finds the names that start with A or B.
SELECT Name FROM Students
WHERE Name LIKE '[A-B]%'
ORDER BY Name;
In SQL Server, [A-B] matches a first letter from A to B, and % matches the rest of the name (SQL Server documentation). A caret after the opening bracket matches the letters outside the set: '[^A-B]%' finds the names whose first letter is outside A to B.
Without a hyphen, the brackets list single letters, as in '[BC]%'. Which letters a range covers depends on the column's collation, the rules that sort its text. Test a range on the database that will run it.
REGEXP, SIMILAR TO and GLOB
MySQL's REGEXP matches a value against a regular expression, a pattern language that has character sets. For example, Name REGEXP '^[A-B]' is MySQL's way to write SQL Server's Name LIKE '[A-B]%'.
SELECT Name FROM Students
WHERE Name REGEXP '^[A-B]'
ORDER BY Name;
| Name |
|---|
| Alice |
| Alina |
| Bob |
Alice, Alina and Bob start with A or B. The ^ ties the match to the start of the value (MySQL manual). A ^ inside the brackets means something else: [^A-B] is one character outside A to B.
Each database writes the same search its own way:
| Database | Names that start with A or B |
|---|---|
| SQL Server | Name LIKE '[A-B]%' |
| MySQL 9.1 | Name REGEXP '^[A-B]' |
| PostgreSQL 18 | Name SIMILAR TO '[A-B]%' or Name ~ '^[A-B]' |
| SQLite 3.50 | Name GLOB '[A-B]*' |
| Oracle | REGEXP_LIKE(Name, '^[A-B]') |
On MySQL, PostgreSQL and SQLite, each form returned Alice, Alina and Bob.
PostgreSQL's SIMILAR TO keeps % and _ and, like LIKE, has to match the whole value. ~ is PostgreSQL's regular expression operator (PostgreSQL 18 documentation). SQLite's GLOB writes * where LIKE writes %, and treats upper and lower case as different letters (SQLite documentation).
ESCAPE
ESCAPE names a character that turns the next % or _ of the pattern into an ordinary character. For example, you can find the usernames that contain an underscore.
SELECT Username FROM Students
WHERE Username LIKE '%!_%' ESCAPE '!'
ORDER BY Username;
| Username |
|---|
| alice_k |
| bob_99 |
!_ matches an underscore, and the % on each side is still a wildcard. alice_k and bob_99 contain an underscore, and alina and charlie don't.
Without ESCAPE, _ is a wildcard again: '%_%' returns all four usernames, because each has at least one character. To search for a percent sign, use the same form: '%!%%' ESCAPE '!' matches 50% off and not 50 off. SQL Server also reads a wildcard inside brackets as an ordinary character, so '%[_]%' searches for an underscore there.
What goes wrong
LIKE with brackets in MySQL or PostgreSQL
MySQL, PostgreSQL, SQLite and Oracle give LIKE only % and _. A bracket is an ordinary character there, so Name LIKE '[A-B]%' returned no rows on MySQL, PostgreSQL and SQLite, and no error. Write the search with your database's operator from the table above, such as REGEXP on MySQL.
[!A-B], * and ? come from Microsoft Access
Some tutorials negate a set with !, as in [!A-B], and use * and ? as wildcards. Those are the wildcards of Microsoft Access (Microsoft Support), and SQL Server negates a set with ^. The * in SELECT * isn't a wildcard either: it selects every column.
A backslash as the escape character
Choose ! or another character that your data doesn't contain. The backslash means something different on each database:
| Pattern | MySQL 9.1 | PostgreSQL 18 | SQLite 3.50 |
|---|---|---|---|
'%!_%' ESCAPE '!' | 2 rows | 2 rows | 2 rows |
'%\_%', no ESCAPE | 2 rows | 2 rows | no rows |
'%\_%' ESCAPE '\' | error 1064 | 2 rows | 2 rows |
MySQL and PostgreSQL use the backslash as the escape character when a pattern has no ESCAPE clause (MySQL manual, PostgreSQL 18 documentation). SQLite, SQL Server and Oracle use an escape character only where ESCAPE names one.
MySQL also reads a backslash inside any string as an escape. In ESCAPE '\', the backslash escapes the closing quote, so the string never ends, and the query fails with syntax error 1064. MySQL accepts the backslash doubled, as ESCAPE '\\'.
A % or _ that a user types
Escape every %, _ and ! in the text that a user types into a search box, then add the wildcards that your query needs. Someone who searches for 50% means the percent sign itself. Pass the finished pattern as a query parameter, a value that the database receives apart from the SQL text.
A leading _ reads every row
EXPLAIN shows the plan, the steps that MySQL takes to run a query. Name is the primary key, so MySQL keeps the names sorted in an index:
EXPLAIN SELECT * FROM Students WHERE Name LIKE 'Ali%';
EXPLAIN SELECT * FROM Students WHERE Name LIKE '_li%';
| pattern | type | key | rows |
|---|---|---|---|
'Ali%' | range | PRIMARY | 2 |
'_li%' | ALL | NULL | 4 |
For 'Ali%', the type is range: MySQL reads only the part of the index where the names start with Ali, 2 rows. For '_li%', the type is ALL: the first letter is unknown, so MySQL reads all 4 rows. A leading _ costs as much as a leading % (MySQL manual).
Run the queries in DbSchema
The DbSchema SQL Editor runs a query and shows its rows in a grid under the editor. Press Editor in the toolbar, choose SQL Editor, paste a query from this article, and press Run.
Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL, SQLite or SQL Server. Paste the CREATE TABLE and INSERT statements into the SQL Editor, then run each pattern and change one wildcard at a time. The free Community Edition includes the database connection and the SQL Editor.

