SQL LIKE Operator: Wildcards, ESCAPE, ILIKE, and Examples
For SQL beginners who know SELECT and WHERE and want to find rows by part of a text value.
On this page
LIKE returns the rows whose text matches a pattern, in which wildcards stand for the characters you don't know. For example, you can find every employee whose email ends in @example.com.
Every LIKE condition has the same shape:
SELECT columns
FROM table_name
WHERE column_name LIKE 'pattern' ESCAPE 'character';
column_nameis the text column to search.LIKEkeeps the rows whose value matches the whole pattern.NOT LIKEkeeps the rows whose value doesn't match.'pattern'is a string of letters and wildcards.%stands for any run of characters, and_for exactly one.ESCAPE 'character'is optional. WithESCAPE '!', the pattern!_matches an underscore instead of any one character.
SQL Wildcard Characters covers every wildcard, including the bracket sets of SQL Server.
Create a sample table of five employees:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO employees VALUES
(1, 'John Doe', '[email protected]'),
(2, 'Jane Doe', '[email protected]'),
(3, 'Sam Smith', '[email protected]'),
(4, 'Mike Johnson', '[email protected]'),
(5, 'Emily Davis', NULL);
Every query below returned the same rows on PostgreSQL 18.6, MySQL 9.1 and SQLite 3.50, except two: ILIKE, and LIKE on a number.
LIKE with %
% matches any run of characters, including none. For example, 'J%' finds every name that starts with J.
SELECT employee_id, name
FROM employees
WHERE name LIKE 'J%'
ORDER BY employee_id;
| employee_id | name |
|---|---|
| 1 | John Doe |
| 2 | Jane Doe |
John Doe and Jane Doe start with J, and % matches the rest of each name. Put the % in front, as in '%son', and the query returns only Mike Johnson. With a % on both sides, '%Doe%' finds Doe anywhere in the name.
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.
LIKE with _
_ matches exactly one character. For example, 'J_n%' finds the names with J as the first letter and n as the third.
SELECT employee_id, name
FROM employees
WHERE name LIKE 'J_n%'
ORDER BY employee_id;
| employee_id | name |
|---|---|
| 2 | Jane Doe |
In Jane Doe, the _ matches the a, and the n follows it. John Doe's third letter is h, where the pattern has n. The % at the end matches the rest of the name.
NOT LIKE
NOT LIKE returns the rows whose value doesn't match the pattern. For example, you can list the employees whose email is outside the example.com domain.
SELECT employee_id, email
FROM employees
WHERE email NOT LIKE '%@example.com'
ORDER BY employee_id;
| employee_id | |
|---|---|
| 3 | [email protected] |
Sam Smith is the only employee with an address at another domain. Emily Davis isn't in the result either. Her email is NULL, which means the email has no value, and neither LIKE nor NOT LIKE returns a row whose value is NULL:
ESCAPE
ESCAPE names a character that turns the next % or _ of the pattern into an ordinary character. For example, you can find the emails that contain an underscore.
SELECT employee_id, email
FROM employees
WHERE email LIKE '%!_%' ESCAPE '!'
ORDER BY employee_id;
| employee_id | |
|---|---|
| 2 | [email protected] |
!_ matches an underscore, and the % before and after !_ are still wildcards. Only Jane Doe's address has an underscore. Without ESCAPE, _ is a wildcard again, and '%_%' returns every email with at least one character: all four that aren't NULL.
Choose an escape character that doesn't occur in your data, such as !. Avoid the backslash: an ESCAPE clause that names it fails on MySQL 9.1 with error 1064. SQL Wildcard Characters shows how each database treats the backslash.
ILIKE and case
ILIKE matches a pattern like LIKE, but treats upper and lower case as the same letter. For example, name ILIKE 'john%' finds John Doe.
SELECT employee_id, name
FROM employees
WHERE name ILIKE 'john%'
ORDER BY employee_id;
| employee_id | name |
|---|---|
| 1 | John Doe |
ILIKE exists only in PostgreSQL (PostgreSQL 18 documentation). With LIKE instead of ILIKE, the same query returns no rows on PostgreSQL. The other databases decide case for LIKE in their own way:
- MySQL 9.1 returns John Doe for
LIKE 'john%', because its default collation, the rules that compare text, ignores case (MySQL 8.4 manual). - SQLite 3.50 returns John Doe too. It ignores case only for the letters A to Z (SQLite documentation).
- SQL Server follows the collation of the column (SQL Server documentation).
To get the same answer on every database, compare the name in lower case with a pattern in lower case. WHERE LOWER(name) LIKE 'john%' returns John Doe on all three.
LIKE with OR
OR joins two LIKE conditions and returns the rows that match either pattern. For example, you can find the names that start with J or with S.
SELECT employee_id, name
FROM employees
WHERE name LIKE 'J%' OR name LIKE 'S%'
ORDER BY employee_id;
| employee_id | name |
|---|---|
| 1 | John Doe |
| 2 | Jane Doe |
| 3 | Sam Smith |
John Doe and Jane Doe match 'J%', and Sam Smith matches 'S%'. SQL has no LIKE IN (...). In PostgreSQL, LIKE ANY takes a list of patterns: name LIKE ANY (ARRAY['J%', 'S%']) returns the same three rows.
What goes wrong
NOT LIKE drops the NULL rows
If the rows without an email belong in the list of addresses outside example.com, add them with IS NULL:
SELECT employee_id, email
FROM employees
WHERE email NOT LIKE '%@example.com'
OR email IS NULL
ORDER BY employee_id;
| employee_id | |
|---|---|
| 3 | [email protected] |
| 5 | NULL |
LIKE on a number
LIKE compares text. MySQL and SQLite convert the number to text first, and return John Doe for this query. PostgreSQL rejects the query:
SELECT employee_id, name
FROM employees
WHERE employee_id LIKE '1%';
ERROR: operator does not exist: integer ~~ unknown
In the error, ~~ is PostgreSQL's name for LIKE. Cast the column to text, as in employee_id::text LIKE '1%', and PostgreSQL returns John Doe too. To find a range of numbers or dates, compare the values with >= and < instead.
A leading % can't use an index
An index keeps a column's values in sorted order, so the database can find a value without reading every row. The sort starts at the first character of each value. An index can find the names that start with abc, but not the names with abc in the middle.
EXPLAIN shows the plan, the steps that PostgreSQL takes to run a query. This PostgreSQL 18 table holds 200,000 names made of hex digits, with an index on them:
CREATE TABLE people AS
SELECT g AS id, md5(g::text) AS name
FROM generate_series(1, 200000) AS g;
CREATE INDEX people_name_idx ON people (name);
ANALYZE people;
EXPLAIN (COSTS OFF) SELECT id FROM people WHERE name LIKE 'abc%';
Seq Scan on people
Filter: (name ~~ 'abc%'::text)
Seq Scan means that PostgreSQL reads every row: it ignores the index, although the pattern starts with letters. The reason is the database's locale, en_US.utf8, which sets how text sorts. Outside the C locale, a B-tree index, the default kind, works for LIKE only when it has a pattern operator class (PostgreSQL 18 documentation):
CREATE INDEX people_name_pattern_idx ON people (name text_pattern_ops);
The same EXPLAIN then shows Index Scan using people_name_pattern_idx. For '%abc%', the plan stays a Seq Scan. With the pg_trgm extension, PostgreSQL can build an index that also finds '%abc%' (PostgreSQL 18 documentation):
CREATE EXTENSION pg_trgm;
CREATE INDEX people_name_trgm_idx ON people USING gin (name gin_trgm_ops);
With that index, EXPLAIN for '%abc%' shows a Bitmap Index Scan on people_name_trgm_idx.
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.
Change one wildcard and run the query again. The number of rows tells you whether the pattern matches more rows or fewer.
Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL or SQLite. Paste the CREATE TABLE and INSERT statements into the SQL Editor, then run each pattern and change it. The free Community Edition includes the database connection and the SQL Editor.

