SQL · Guides · October 30, 2026 · 10 min read

SQL LIKE operator: % and _ wildcards, ILIKE, NOT LIKE and escaping

LIKE filters text by a pattern instead of an exact value. A pattern has two wildcards: % matches any sequence of characters, including none, and _ matches exactly one character. So city LIKE 'Mos%' finds every city that starts with “Mos”, model LIKE '%300' finds models that end in “300”, and airport_name LIKE '%ovo%' finds names that contain “ovo” anywhere. LIKE is case-sensitive in PostgreSQL and DuckDB; ILIKE is the case-insensitive version. NOT LIKE reverses the match, but neither of them ever returns rows where the column is NULL.

All examples run on the airline database from the Nomto SQL course, in PostgreSQL syntax on DuckDB (the engine of the Nomto SQL room), and every result table is the real output.

LIKE syntax

SELECT columns
FROM table
WHERE text_column LIKE 'pattern';

The pattern is an ordinary string in single quotes. Without wildcards, LIKE behaves like =; it is the % and _ that make it useful. The pattern has to match the whole value, which is why the position of % matters so much:

The % wildcard: starts with, ends with, contains

Starts with

SELECT airport_code, airport_name, city
FROM airports
WHERE city LIKE 'Mos%'
ORDER BY airport_code;

Ends with

SELECT model
FROM aircrafts
WHERE model LIKE '%300'
ORDER BY model;

Contains

% on both sides means “anywhere in the string”:

SELECT airport_name, city
FROM airports
WHERE airport_name LIKE '%ovo%'
ORDER BY airport_name
LIMIT 6;

The full result has 12 airports; the query is cut at six rows here.

“Contains” catches more than you may intend. Passenger names are stored as “FIRST LAST” in capitals. Searching for the surname Ivanov with '%IVANOV%' returns 14,040 tickets, but '% IVANOV' (a space, then the surname at the very end) returns only 7,539:

SELECT CASE WHEN passenger_name LIKE '% IVANOV'  THEN 'IVANOV'
            WHEN passenger_name LIKE '% IVANOVA' THEN 'IVANOVA'
            ELSE 'other' END AS surname,
       COUNT(*) AS tickets
FROM tickets
WHERE passenger_name LIKE '%IVANOV%'
GROUP BY surname
ORDER BY tickets DESC;

The loose pattern silently picked up the feminine form Ivanova too. Anchor the pattern as tightly as the data allows.

The _ wildcard: exactly one character

_ stands for one character, no more and no fewer. Five underscores find five-letter cities:

SELECT city
FROM airports
WHERE city LIKE '_____'
ORDER BY city;

Mixing both wildcards: 'S_r%' means S, then any single character, then r, then anything.

SELECT city FROM airports WHERE city LIKE 'S_r%' ORDER BY city;

Strezhevoy is not a mistake: S, then “t” for the underscore, then “r”. _ matches any character, consonants included.

Case-insensitive search: ILIKE and LOWER

In PostgreSQL and DuckDB, LIKE compares characters exactly, so lower case does not match upper case:

SELECT 'ABC' LIKE 'abc' AS like_result, 'ABC' ILIKE 'abc' AS ilike_result;

WHERE city LIKE 'mos%' returns no rows; WHERE city ILIKE 'mos%' returns the three Moscow airports. ILIKE is not standard SQL: it exists in PostgreSQL, DuckDB and Snowflake, but not in MySQL or SQL Server. The portable way is to lower both sides:

SELECT city FROM airports WHERE lower(city) LIKE 'mos%';

In MySQL and SQL Server, case sensitivity depends on the column’s collation, and the default collations are case-insensitive, so plain LIKE 'mos%' already finds Moscow there. That difference is a common surprise when a query moves between databases.

NOT LIKE, and why NULL rows disappear

NOT LIKE keeps the rows that do not match. The trap: if the column is NULL, both LIKE and NOT LIKE return NULL, and WHERE drops the row either way. About half the tickets have an email in contact_data; split them by whether the address contains an underscore (how \_ works is explained in the next section):

SELECT COUNT(*) FILTER (WHERE contact_data->>'email' LIKE '%\_%' ESCAPE '\')     AS with_underscore,
       COUNT(*) FILTER (WHERE contact_data->>'email' NOT LIKE '%\_%' ESCAPE '\') AS without_underscore,
       COUNT(*) FILTER (WHERE contact_data->>'email' IS NULL)                    AS no_email,
       COUNT(*) AS total
FROM tickets;

LIKE plus NOT LIKE gives 199,083, not 366,733: the 167,650 tickets with no email are in neither group. If “doesn’t match” should include “has no value”, say so explicitly: WHERE email NOT LIKE '%\_%' ESCAPE '\' OR email IS NULL, or WHERE COALESCE(email, '') NOT LIKE .... The NULL guide explains the three-valued logic behind this.

Escaping: how to search for a literal % or _

Since % and _ are wildcards, you need an escape character to search for the characters themselves. Without it, “emails containing an underscore” matches every email:

SELECT COUNT(*) AS emails,
       COUNT(*) FILTER (WHERE email LIKE '%_%')               AS unescaped,
       COUNT(*) FILTER (WHERE email LIKE '%\_%' ESCAPE '\')   AS escaped
FROM (SELECT contact_data->>'email' AS email FROM tickets) t
WHERE email IS NOT NULL;

'%_%' means “at least one character of any kind”, which every non-empty string satisfies. With ESCAPE '\', the sequence \_ means a real underscore.

The default escape character differs between databases, and this is where queries break when you copy them:

• PostgreSQL treats backslash as the escape character by default, so LIKE '%\_%' works without an ESCAPE clause.
• DuckDB has no default escape character. The same LIKE '%\_%' without ESCAPE returns 0 rows on this data, because it looks for an actual backslash. Always write ESCAPE '\'.
• MySQL uses backslash by default, like PostgreSQL.
• SQL Server has no default either, but also lets you put a wildcard in square brackets: LIKE '%[_]%'. In other databases brackets are ordinary characters (DuckDB returns 0 rows for that pattern).

Writing an explicit ESCAPE clause works in all of them, and you can pick any character, for example LIKE '%!_%' ESCAPE '!'.

Several patterns at once

LIKE takes one pattern. For several, combine conditions with OR, and wrap them in parentheses if there are other conditions in the WHERE:

SELECT city
FROM airports
WHERE (city LIKE 'Nov%' OR city LIKE 'Nor%')
ORDER BY city;

Without the parentheses, WHERE a AND b OR c is read as (a AND b) OR c, a classic source of extra rows.

PostgreSQL also accepts city LIKE ANY (ARRAY['Nov%', 'Nor%']). DuckDB does not: it fails with Unsupported comparison "~~" for ANY/ALL subquery, so in the Nomto SQL room use OR.

When the patterns get complicated, switch to regular expressions. In DuckDB, regexp_matches(city, '^(Nov|Nor|Noy)') returns Norilsk, Novokuznetsk, Novosibirsk, Novy Urengoy and Noyabrsk. PostgreSQL uses the operator city ~ '^(Nov|Nor|Noy)'. Be careful with ~ in DuckDB: there it requires the regex to match the whole string, so city ~ '^Nov' returns nothing and you would need city ~ 'Nov.*'.

LIKE vs =

Use = whenever you know the full value. LIKE without wildcards gives the same result as = in PostgreSQL and DuckDB ('abc ' LIKE 'abc' and 'abc ' = 'abc' are both false). In SQL Server, = ignores trailing spaces but LIKE treats them as significant, so 'abc ' = 'abc' is true while 'abc' LIKE 'abc ' is false there.

LIKE also works on dates turned into text, CAST(scheduled_departure AS VARCHAR) LIKE '2026-08-01%', and here it gives the same 568 flights as a range. Prefer the range, scheduled_departure >= '2026-08-01' AND scheduled_departure < '2026-08-02': it keeps the column’s type, works in every database whatever the text format of dates, and can use an index. More on this in the SQL date functions guide.

LIKE performance

On a large table, how the pattern starts decides how fast it runs:

• A fixed prefix ('Mos%') can use a B-tree index, because all matching values sit next to each other in sorted order. In PostgreSQL this works out of the box only with the C collation; with other collations you need an index built with text_pattern_ops (or varchar_pattern_ops).
• A leading wildcard ('%ovo' or '%ovo%') cannot use a normal index: the database has to read every value. In PostgreSQL, a trigram index (the pg_trgm extension with a GIN or GiST index) makes “contains” searches fast.
• lower(col) LIKE ... and ILIKE do not use a plain index on col. In PostgreSQL, create an index on lower(col) for the first; ILIKE needs a trigram index.

Columnar analytical engines like DuckDB, BigQuery or Snowflake scan columns quickly anyway, so on a course-sized dataset you will not notice the difference. On a production PostgreSQL or MySQL table with millions of rows, you will.

Common mistakes

• Forgetting the wildcard. WHERE city LIKE 'Mos' only finds a city called exactly “Mos”.
• Double quotes around the pattern. In PostgreSQL and DuckDB, "Mos%" is a column name, not a string. Use single quotes.
• % on both sides when you meant a prefix. Slower and matches too much, as the Ivanov/Ivanova example shows.
• Unescaped _ in the pattern. LIKE 'view_%' matches “view_offer”, but also “viewX” or “views”; escape it if you mean a real underscore.
• Expecting NOT LIKE to return NULL rows. It never does; add OR col IS NULL.
• Mixing AND and OR without parentheses when combining several patterns.

For pulling parts out of strings once you have found them (SPLIT_PART, SUBSTRING, REPLACE), see the SQL string functions guide. Pattern filters are also one of the topics in the SQL cheat sheet.

FAQ

What does % mean in SQL LIKE?

% matches any sequence of characters, including an empty one. 'Mos%' matches “Mos”, “Moscow” and anything else starting with “Mos”. _ matches exactly one character.

Is SQL LIKE case-sensitive?

In PostgreSQL, DuckDB and Oracle, yes. In MySQL and SQL Server it depends on the collation, and the default collations are case-insensitive. For a case-insensitive match in PostgreSQL or DuckDB use ILIKE, or lower(col) LIKE 'pattern' in any database.

How do I escape an underscore or percent sign in LIKE?

Add an escape character before it and declare it with ESCAPE: col LIKE '%\_%' ESCAPE '\' finds values with a real underscore. PostgreSQL and MySQL use backslash by default; DuckDB and SQL Server do not, so the explicit ESCAPE clause is the portable form. SQL Server also accepts '%[_]%'.

Can I use LIKE with multiple values?

Not directly. Combine conditions with OR (city LIKE 'Nov%' OR city LIKE 'Nor%'), use LIKE ANY (ARRAY[...]) in PostgreSQL, or switch to a regular expression.

Is LIKE slower than =?

A LIKE with a fixed prefix can use an index and is almost as fast as =. A pattern that starts with % has to check every row, which is much slower on large tables unless you add a trigram index (PostgreSQL) or a full-text index.

Does LIKE work with numbers?

In PostgreSQL you have to cast first: CAST(col AS TEXT) LIKE '12%'. MySQL converts automatically. For numbers, a range condition (col BETWEEN 1200 AND 1299) is almost always the better choice.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文