Back to all posts

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_name is 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 pattern A_i% read against each student: Alice and Alina match A, one character, i, and the rest; Bob and Charlie fail on their first letter

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:

DatabaseNames that start with A or B
SQL ServerName LIKE '[A-B]%'
MySQL 9.1Name REGEXP '^[A-B]'
PostgreSQL 18Name SIMILAR TO '[A-B]%' or Name ~ '^[A-B]'
SQLite 3.50Name GLOB '[A-B]*'
OracleREGEXP_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:

PatternMySQL 9.1PostgreSQL 18SQLite 3.50
'%!_%' ESCAPE '!'2 rows2 rows2 rows
'%\_%', no ESCAPE2 rows2 rowsno rows
'%\_%' ESCAPE '\'error 10642 rows2 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%';
patterntypekeyrows
'Ali%'rangePRIMARY2
'_li%'ALLNULL4

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.

The LIKE 'A_i%' query in the DbSchema SQL Editor, with Alice and Alina in the result grid under the editor

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.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download