SQL LIKE Operator: Wildcards, ESCAPE, ILIKE, and Examples

Somebody types part of a name into a search box, the query compares it with =, and the answer comes back empty even though the row is there. = matches a value only when the whole cell is that value. LIKE compares against a pattern instead: you write the part you know, mark the rest with % or _, and the database returns every row that fits.

What the SQL LIKE operator does

LIKE is a condition, so it lives in a WHERE clause with a column on the left and a pattern on the right:

SELECT column1, column2
FROM table_name
WHERE column_name LIKE 'pattern';

The pattern is an ordinary string in quotes. Every character in it has to match the value exactly, apart from the wildcards:

Every example below runs against these five rows:

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',  '[email protected]');

For the wildcards on their own, with a table of patterns to copy, read SQL Wildcard Characters. For the clause LIKE sits in, see SQL WHERE Clause and SQL SELECT.

Pattern matching examples

A % at the end of the pattern searches by prefix, which is what an autocomplete box does with the letters typed so far:

SELECT employee_id, name
FROM employees
WHERE name LIKE 'J%'
ORDER BY employee_id;

Move the % to the front and the pattern searches by suffix:

SELECT employee_id, name
FROM employees
WHERE name LIKE '%son'
ORDER BY employee_id;

One at each end finds the fragment anywhere in the value, which is the pattern behind most search boxes:

SELECT employee_id, name
FROM employees
WHERE name LIKE '%Davis%'
ORDER BY employee_id;

_ is the wildcard for a position rather than a run. One underscore stands for one character that has to be there, so this pattern asks for a name whose second letter is a:

SELECT employee_id, name
FROM employees
WHERE name LIKE '_a%'
ORDER BY employee_id;

The two wildcards combine into a pattern with fixed points and free letters between them. Read 'J_n%' as a J, then any single character, then an n:

SELECT employee_id, name
FROM employees
WHERE name LIKE 'J_n%'
ORDER BY employee_id;

John Doe looks like it should be here and is not. Counting the letters shows why: the third character of John is h, and the pattern wants an n in that position.

Excluding rows with NOT LIKE

NOT LIKE returns everything the pattern did not match, which is how you list the values that break a convention rather than the ones that keep it:

SELECT employee_id, email
FROM employees
WHERE email NOT LIKE '%@example.com'
ORDER BY employee_id;

One address out of five is on another domain. The same query shape finds the codes without the prefix your convention requires, or the files whose names do not end in the extension you expect. Join several NOT LIKE conditions with AND when one pattern is too broad, and each condition narrows the list further.

ILIKE and case-insensitive search

Whether LIKE 'john%' finds John Doe depends on the engine and the collation, not on the pattern. PostgreSQL 17 matches case for case, and adds ILIKE, a PostgreSQL extension that matches case-insensitively according to the active locale (PostgreSQL 17 documentation):

SELECT employee_id, name
FROM employees
WHERE name ILIKE 'john%'
ORDER BY employee_id;

Where the engine has no ILIKE, put both sides into the same case yourself:

SELECT employee_id, name
FROM employees
WHERE LOWER(name) LIKE 'john%'
ORDER BY employee_id;

Both queries return the same row, and the second one runs anywhere. The cost is that an index on name no longer matches the condition, because the condition is now on LOWER(name) and not on the column.

ESCAPE for literal % and _

When the value itself contains a % or a _, the pattern needs a way to say that this one is a character. The ESCAPE clause names the character that turns off the wildcard that follows it:

SELECT employee_id, email
FROM employees
WHERE email LIKE '%\_%' ESCAPE '\'
ORDER BY employee_id;

\_ is a literal underscore, and the % on either side of it is still the wildcard, so the pattern reads as "an underscore somewhere in the address". Any single character can be the escape character; the backslash is the usual pick. A literal percent sign works the same way, as LIKE '%50\%%' ESCAPE '\' for values containing the text 50%, and SQL Wildcard Characters shows that one with its rows.

LIKE vs = vs REGEXP

The three operators answer three different questions, and picking the wrong one is what makes a search return nothing or everything.

Use = when you know the whole value, because it is shorter and the column's index applies to it directly. Use LIKE when the user typed part of the value. Go to a regular expression only when the rule stops being about position: three letters followed by two digits, or either of two spellings, is more than % and _ can express.

Database support and performance tips

All five engines below accept %, _ and an ESCAPE clause. What differs is the case, and whether the engine gives you a separate operator for it.

MySQL 8.4 is the one that surprises people: its string comparisons are not case-sensitive unless one operand uses a case-sensitive collation or is a binary string (MySQL 8.4 manual), so LIKE 'john%' finds John Doe there without ILIKE.

SQLite splits the question by character set. Its documentation says SQLite "only understands upper/lower case for ASCII characters by default" and that LIKE "is case sensitive by default for unicode characters that are beyond the ASCII range", so 'a' LIKE 'A' is true and 'æ' LIKE 'Æ' is false (SQLite documentation). The case_sensitive_like pragma makes the operator case sensitive.

Where the wildcard sits decides whether an index helps. PostgreSQL 17 can use a B-tree index for a LIKE pattern that is a constant anchored to the beginning of the string, col LIKE 'foo%' but not col LIKE '%bar' (PostgreSQL 17 documentation). For the searches that do start with a wildcard, the pg_trgm module provides GiST and GIN operator classes that support index searches for LIKE and ILIKE, and there the search string need not be left-anchored at all (PostgreSQL 17 documentation). That is the answer to a slow %text% search, rather than giving up on the index.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Run LIKE queries visually in DbSchema

Getting a pattern right takes a few attempts, and each attempt is worth running against real data rather than the five rows above. Connect DbSchema to the database through its PostgreSQL, MySQL or SQL Server JDBC driver, write the query in the DbSchema SQL Editor, and press "Execute Query" to run the statement at the cursor and get the rows in the result pane. Change one wildcard, press it again, and the row count tells you whether the pattern got wider or narrower.

When a NOT LIKE query is the exception list somebody has to work through, the Save button in the DbSchema result pane exports the full result set to a file: the query is re-executed and every row is written to disk, including the ones that never fit on the screen.

Both of those only read. A SELECT leaves the database as it was, and neither running a query nor exporting its rows touches the design model file.

Point DbSchema at the table your search box will query and the pattern stops being guesswork. Download DbSchema at https://dbschema.com/download.html, connect to your database, and run the patterns above in the SQL Editor of the free Community Edition.

FAQ

Can LIKE be used on a number or a date column?

In SQL Server it can: when an argument is not a character string type, the database converts it to one if that is possible (SQL Server documentation). A numeric comparison or a date range is the better tool, because it uses the column's index and does not depend on how the value is formatted as text.

Is there a limit on how long a LIKE pattern can be?

In SQL Server the pattern can be a maximum of 8,000 bytes. A pattern that long is a sign the search rule belongs in a regular expression or in full-text search rather than in LIKE.