SQL LIKE Operator: Wildcards, NOT LIKE and Examples

LIKE filters rows by a text pattern instead of an exact value. It is how you find names that start with a letter, emails that end in a domain, or notes that contain a word. Here is the syntax, both wildcards, and worked examples with real output.

By Matthew Rathbone · November 12, 2026

The SQL LIKE operator tests whether a text value matches a pattern. You use it in a WHERE clause, and the pattern is ordinary text plus two wildcards: % for any number of characters and _ for exactly one.

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

Jump to: wildcards · sample data · examples · NOT LIKE · multiple values · case sensitivity and ILIKE · escaping % and _ · differences by database · performance · mistakes · FAQ

SQL LIKE wildcards

Standard SQL has two wildcards, and they work in every database:

Wildcard	Matches	Example	Finds
`%`	Zero, one or many characters	`LIKE 'Jo%'`	Jo, Jon, John, Joanna
`_`	Exactly one character	`LIKE 'Jo_n'`	John, Joan (not Jon)

Combine them to build the four patterns you will use most:

Goal	Pattern
Starts with `abc`	`LIKE 'abc%'`
Ends with `abc`	`LIKE '%abc'`
Contains `abc` anywhere	`LIKE '%abc%'`
Starts with `a` and ends with `c`	`LIKE 'a%c'`
Second character is `b`	`LIKE '_b%'`
Exactly three characters	`LIKE '___'`
At least three characters	`LIKE '___%'`

A pattern with no wildcard at all, such as LIKE 'abc', only matches the whole value abc. LIKE always compares against the entire string, so "contains" needs a % on both sides.

Sample data

Every query below was run on SQLite 3.45 and the output is copied from the real result. The queries are standard SQL, so they also run on MySQL, PostgreSQL, SQL Server and Oracle. The one thing that changes between databases is upper and lower case, covered in its own section.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, city TEXT, promo_code TEXT);
INSERT INTO customers VALUES
 (1, 'Anna Jackson',   '[email protected]',   'Austin',        'SAVE10'),
 (2, 'Andrew Olson',   '[email protected]', 'San Diego',     'SAVE25'),
 (3, 'Brianna Lee',    '[email protected]',  'San Francisco', '50%OFF'),
 (4, 'Jon Smith',      '[email protected]',    'Boston',        'VIP_2026'),
 (5, 'John Peterson',  '[email protected]',    'Santa Fe',      'SAVE5'),
 (6, 'Joan Rivera',    '[email protected]',   'austin',        NULL),
 (7, 'Dana Anderson',  NULL,                 'New York',      'VIP2026');

SQL LIKE examples

1. Starts with

Put the text first and a % after it:

SELECT name FROM customers WHERE name LIKE 'An%';

name
Anna Jackson
Andrew Olson

Dana Anderson is not returned. Her name contains An, but it does not start with it.

2. Ends with

Flip it around: % first, then the text.

SELECT name, email FROM customers WHERE email LIKE '%@gmail.com';

name          | email
Anna Jackson  | [email protected]
Brianna Lee   | [email protected]
John Peterson | [email protected]

SQLite returned the upper-case [email protected] as well. PostgreSQL and Oracle would not; see case sensitivity.

3. Contains

To check whether a string contains some text anywhere, wrap it in % on both sides:

SELECT name FROM customers WHERE name LIKE '%son%';

name
Anna Jackson
Andrew Olson
John Peterson
Dana Anderson

Because % also matches zero characters, '%son%' finds son at the start, the middle or the very end. This is the usual answer to "SQL contains". (SQL Server does have a CONTAINS function, but it only works on columns with a full-text index.)

4. The underscore: exactly one character

SELECT name FROM customers WHERE name LIKE 'Jo_n %';

name
John Peterson
Joan Rivera

Jo_n needs exactly one character between Jo and n, so Jon Smith is left out. Swap the underscore for a percent sign and he comes back, because % is happy to match nothing:

SELECT name FROM customers WHERE name LIKE 'Jo%n %';

name
Jon Smith
John Peterson
Joan Rivera

5. Match a fixed length

Several underscores in a row pin down the length. This finds promo codes that are SAVE plus exactly two more characters:

SELECT promo_code FROM customers WHERE promo_code LIKE 'SAVE__';

promo_code
SAVE10
SAVE25

SAVE5 is too short to match.

6. LIKE inside CASE

LIKE is a condition, so it works anywhere a condition does, including a CASE statement. Here it labels each customer by email provider:

SELECT name,
       CASE WHEN email LIKE '%@gmail.com'   THEN 'Gmail'
            WHEN email LIKE '%@outlook.com' THEN 'Outlook'
            WHEN email IS NULL              THEN 'none'
            ELSE 'other' END AS provider
FROM customers;

name          | provider
Anna Jackson  | Gmail
Andrew Olson  | Outlook
Brianna Lee   | Gmail
Jon Smith     | other
John Peterson | Gmail
Joan Rivera   | other
Dana Anderson | none

7. Build the pattern from a value

The pattern does not have to be typed out by hand. Join the wildcards to a column, a variable or a query parameter with the concatenation operator:

SELECT name FROM customers WHERE name LIKE '%' || 'lee' || '%';

name
Brianna Lee

Use CONCAT('%', ?, '%') in MySQL and '%' + @term + '%' in SQL Server. In application code, pass the search term as a parameter like this rather than pasting it into the SQL string, which protects you from SQL injection.

NOT LIKE

NOT LIKE returns the rows that do not match the pattern:

SELECT name, city FROM customers WHERE city NOT LIKE 'San%';

name          | city
Anna Jackson  | Austin
Jon Smith     | Boston
Joan Rivera   | austin
Dana Anderson | New York

There is one trap. A NULL is neither like nor not like anything, so rows with a NULL in the column disappear from both results:

SELECT name, email FROM customers WHERE email NOT LIKE '%@gmail.com';

name         | email
Andrew Olson | [email protected]
Jon Smith    | [email protected]
Joan Rivera  | [email protected]

Dana Anderson has no email and is missing. If "not a Gmail address" should include people with no address at all, say so:

SELECT name, email FROM customers
WHERE email NOT LIKE '%@gmail.com' OR email IS NULL;

name          | email
Andrew Olson  | [email protected]
Jon Smith     | [email protected]
Joan Rivera   | [email protected]
Dana Anderson | NULL

More on this in how NULL values work in SQL.

SQL LIKE with multiple values

IN does not understand wildcards, so WHERE city IN ('San%', 'New%') looks for a city literally named San%. To match several patterns, join LIKE conditions with OR:

SELECT name, city FROM customers
WHERE city LIKE 'San%' OR city LIKE 'New%' OR city LIKE 'Bos%';

name          | city
Andrew Olson  | San Diego
Brianna Lee   | San Francisco
Jon Smith     | Boston
John Peterson | Santa Fe
Dana Anderson | New York

Notice Santa Fe: 'San%' matches anything that begins with those three letters. Use 'San %' (with a space) if you only want the two-word cities.

Use AND when every pattern must hold, for example cities starting with San but not ending in Diego:

SELECT name, city FROM customers
WHERE city LIKE 'San%' AND city NOT LIKE '%Diego';

name          | city
Brianna Lee   | San Francisco
John Peterson | Santa Fe

When you mix OR with other conditions, wrap the OR group in parentheses (why). Some databases offer a shorter spelling for long lists:

Database	Shortcut for several patterns
PostgreSQL	`city LIKE ANY (ARRAY['San%', 'New%', 'Bos%'])` or `city ~ '^(San\|New\|Bos)'`
MySQL	`city REGEXP '^(San\|New\|Bos)'`
Oracle	`REGEXP_LIKE(city, '^(San\|New\|Bos)')`
SQL Server, SQLite	Use `OR`, or join to a table of patterns: `JOIN patterns p ON c.city LIKE p.pattern`

Is SQL LIKE case sensitive? (LIKE vs ILIKE)

It depends on the database, which is the most common reason a LIKE query "works on my machine" and nowhere else.

Database	Default behaviour of LIKE	To ignore case
PostgreSQL	Case-sensitive	`ILIKE`, or `LOWER(col) LIKE 'abc%'`
Oracle	Case-sensitive	`LOWER(col) LIKE 'abc%'`
MySQL	Case-insensitive with the default collation	Already does. Use `LIKE BINARY` to make it case-sensitive
SQL Server	Follows the column collation, which is case-insensitive by default	Already does. Add `COLLATE Latin1_General_CS_AS` to make it case-sensitive
SQLite	Case-insensitive for the letters A to Z only	Already does for plain English text

You can see SQLite's behaviour in the sample data. LIKE ignores case, but = does not:

SELECT name, city FROM customers WHERE city LIKE 'austin';

name         | city
Anna Jackson | Austin
Joan Rivera  | austin

SELECT name, city FROM customers WHERE city = 'austin';

name        | city
Joan Rivera | austin

The portable way to get a case-insensitive match is to lower-case both sides. It returns the same rows in every database:

SELECT name, email FROM customers WHERE LOWER(email) LIKE '%@gmail.com';

name          | email
Anna Jackson  | [email protected]
Brianna Lee   | [email protected]
John Peterson | [email protected]

ILIKE is a case-insensitive version of LIKE. It is not standard SQL, but PostgreSQL, Snowflake, Redshift, DuckDB and Databricks all support it: WHERE email ILIKE '%@gmail.com'. MySQL, SQL Server, SQLite and Oracle do not have it, so use LOWER() there.

Escaping % and _ (finding a literal wildcard)

What if the text you are looking for contains a percent sign or an underscore? Searching for promo codes with an underscore like this goes wrong:

SELECT promo_code FROM customers WHERE promo_code LIKE '%_%';

promo_code
SAVE10
SAVE25
50%OFF
VIP_2026
SAVE5
VIP2026

Every code matched, because _ means "any one character". The ESCAPE clause names a character that switches a wildcard back into ordinary text:

SELECT promo_code FROM customers WHERE promo_code LIKE '%\_%' ESCAPE '\';

promo_code
VIP_2026

Any character can be the escape character. Here it is !, used to find a literal percent sign:

SELECT promo_code FROM customers WHERE promo_code LIKE '%!%%' ESCAPE '!';

promo_code
50%OFF

Read that pattern as: anything, then a real %, then anything. MySQL and PostgreSQL treat backslash as the escape character even without an ESCAPE clause; SQL Server, SQLite and Oracle have no default, so spell it out. In SQL Server you can also put the wildcard in square brackets: LIKE '%[_]%'.

LIKE differences by database

Database	Extra wildcards	Default escape	Beyond LIKE
MySQL	None	`\`	`REGEXP` / `REGEXP_LIKE`
PostgreSQL	None	`\`	`ILIKE`, `SIMILAR TO`, regex with `~`
SQL Server	`[abc]` one of these, `[a-f]` a range, `[^abc]` none of these	None	`PATINDEX`, `CONTAINS` (full-text)
SQLite	None	None	`GLOB` (case-sensitive, uses `*` and `?`)
Oracle	None	None	`REGEXP_LIKE`

The SQL Server bracket wildcards are handy: WHERE name LIKE '[A-C]%' finds names starting with A, B or C, and LIKE '[^0-9]%' finds values that do not start with a digit. They are a SQL Server (and Sybase) extension; elsewhere, use a regular expression. The official references have the full detail: PostgreSQL pattern matching, SQL Server LIKE, MySQL LIKE and SQLite LIKE.

LIKE vs =

= compares whole values and treats % and _ as plain characters. LIKE understands wildcards. Without a wildcard the two look the same, but they can still differ on case (as SQLite showed above) and on trailing spaces, so use = for exact matches and keep LIKE for patterns.

LIKE on numbers and dates

LIKE is a text operator. Most databases will quietly convert a number to text first (WHERE id LIKE '1%' runs in MySQL, SQLite and SQL Server), but PostgreSQL refuses until you cast it: WHERE id::text LIKE '1%'. For dates, a range such as WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' is both safer and faster than LIKE '2026%'.

Performance: the leading wildcard

Where the % sits decides whether an index can help:

LIKE 'San%' (starts with) can use an index on the column. The database jumps straight to the entries beginning with San, like opening a dictionary at the right letter.
LIKE '%Diego' or LIKE '%son%' (leading wildcard) cannot. Every row has to be read and checked.

With an index on city, SQLite's query planner shows exactly that:

EXPLAIN QUERY PLAN SELECT name FROM customers WHERE city LIKE 'San%';
SEARCH customers USING INDEX idx_city (city>? AND city<?)

EXPLAIN QUERY PLAN SELECT name FROM customers WHERE city LIKE '%Diego';
SCAN customers

On a few thousand rows a scan is instant and not worth worrying about. On millions of rows, a "contains" search is a job for full-text search (FTS in SQLite, FULLTEXT in MySQL, tsvector or the pg_trgm extension in PostgreSQL). Wrapping the column in LOWER() also skips a plain index, so on large tables index the lower-cased expression or use a case-insensitive collation.

Common mistakes

Forgetting the wildcard. WHERE name LIKE 'son' only matches a name that is exactly son. For "contains" write '%son%'.
Using * and ?. Those are file-search wildcards (and Microsoft Access). SQL uses % and _.
Using = with a wildcard. WHERE name = 'An%' looks for the literal text An%.
Putting wildcards in IN. IN ('San%', 'New%') does not pattern match. Use OR (see multiple values).
Assuming case rules. A query tested on MySQL or SQLite can return fewer rows on PostgreSQL. Use LOWER() or ILIKE when case should not matter.
Forgetting NULLs with NOT LIKE. Rows with NULL are dropped. Add OR col IS NULL if you want them.
Matching more than you meant. 'San%' also finds Santa Fe, and '%son%' finds Jackson. Add a space or more context to tighten the pattern.
Not escaping _ and %. An underscore in the search text is a wildcard unless you escape it.
Double quotes around the pattern. Standard SQL uses single quotes for text: LIKE 'A%'. Double quotes mean a column name in PostgreSQL and Oracle.

FAQ

What does LIKE mean in SQL?

LIKE is an operator that checks whether a text value matches a pattern. It returns true when the value fits, so WHERE name LIKE 'A%' keeps every row whose name starts with A.

What does the % symbol mean in a SQL LIKE clause?

% is a wildcard that stands for any sequence of characters, including none. '%a' means "ends with a", 'a%' means "starts with a" and '%a%' means "contains a".

What is the difference between % and _ in SQL?

% matches zero or more characters. _ matches exactly one. 'J%n' matches Jn, Jon and Jonathan; 'J_n' matches only three-character values such as Jon and Jan.

How do I check if a string contains text in SQL?

Use WHERE col LIKE '%text%'. Alternatives that return a position instead are INSTR (MySQL, SQLite, Oracle), CHARINDEX (SQL Server) and POSITION (PostgreSQL).

How do I use LIKE with multiple values?

Combine conditions with OR: WHERE col LIKE 'a%' OR col LIKE 'b%'. PostgreSQL also accepts LIKE ANY (ARRAY['a%', 'b%']), and MySQL can use REGEXP.

Is LIKE case sensitive in SQL?

In PostgreSQL and Oracle, yes. In MySQL and SQL Server, not by default. SQLite ignores case for A to Z. Use LOWER(col) LIKE 'abc%' for the same result everywhere.

What is the difference between LIKE and ILIKE?

LIKE follows the database's normal case rules. ILIKE always ignores case. ILIKE exists in PostgreSQL, Snowflake, Redshift, DuckDB and Databricks, but not in MySQL, SQL Server, SQLite or Oracle.

Can I use LIKE with numbers?

Yes in most databases, which convert the number to text first. In PostgreSQL, cast it yourself: WHERE CAST(id AS TEXT) LIKE '1%'.

Does LIKE use an index?

Only when the pattern starts with fixed text, such as 'abc%'. A pattern that starts with % forces a full scan.

What is the opposite of LIKE?

NOT LIKE. It returns the rows that do not match the pattern, and skips rows where the column is NULL.

Practice

The fastest way to make this stick is to type a few patterns yourself. The free interactive LIKE lesson runs in your browser with no signup, and the SQL practice problems include pattern-matching questions with solutions. Once you can find text, the next step is changing it: SUBSTRING cuts a piece out of a string and REPLACE swaps one piece for another. For a longer tour of wildcards see how to use wildcards in SQL, and keep the SQL cheat sheet open for the rest of the WHERE operators.

SQL LIKE: Pattern Matching With Wildcards (With Examples)