SQL LIKE and NOT LIKE Operators Explained with Examples

LIKE and NOT LIKE are SQL operators used in a WHERE clause to include or exclude rows based on text patterns. LIKE returns matches, and NOT LIKE returns non-matches, using % for zero or more characters and _ for exactly one character. This tutorial covers MySQL, PostgreSQL, SQL Server, and Snowflake, and shows multi-pattern matching, common NOT LIKE pitfalls, case sensitivity rules, and performance behavior.

• LIKE returns rows whose value matches a pattern, and NOT LIKE returns rows whose value does not match.
• % matches zero or more characters, and _ matches exactly one character.
• SQL has no LIKE IN syntax, so chain conditions with OR, or chain NOT LIKE with AND for exclusions.
• NOT LIKE excludes NULL rows, so combine it with IS NULL when you want them included.
• Case sensitivity depends on the engine and column collation, and PostgreSQL uses ILIKE for case-insensitive matches.
• A leading % wildcard prevents standard B-tree index use and forces a full scan.
• PostgreSQL and Snowflake support LIKE ANY for matching against a list of patterns in one expression, and Snowflake also supports LIKE ALL.
• MySQL and SQL Server have no LIKE ANY and require OR-chained conditions.

The SQL LIKE operator filters rows by matching a column value against a pattern.

Basic Syntax

You can use LIKE in SELECT, UPDATE, and DELETE statements anywhere a WHERE clause is valid, including a SELECT statement.

SELECT column_list FROM table_name WHERE column_name LIKE pattern;

UPDATE table_name SET column_name = value WHERE column_name LIKE pattern;

DELETE FROM table_name WHERE column_name LIKE pattern;

How Pattern Matching Works in SQL

A LIKE pattern is a string literal that mixes fixed characters with wildcards, and each character in the pattern acts as a position anchor. The engine reads the pattern left to right and tries to align each row value against those anchors in order. Take the pattern 'A_i%' against the value Amit Sharma:

• Position 1 (A) must match exactly. Amit Sharma starts with A, so the first anchor passes.
• Position 2 (_) matches any single character. m satisfies the wildcard.
• Position 3 (i) must match exactly. The third character of Amit Sharma is i, so this anchor passes.
• The trailing % matches zero or more characters, so anything from position 4 onward is accepted.

The full comparison respects the column’s collation rules, which decide whether A and a are treated as the same character. NULL values do not match any pattern, including NOT LIKE patterns, because comparisons against NULL evaluate to UNKNOWN rather than TRUE or FALSE.

In SQL pattern matching, wildcards define flexible character positions.

[charlist] and [^charlist] are SQL Server extensions, and they are not part of standard SQL.

Using the Percent Sign Wildcard

The % wildcard matches zero or more characters in the target string.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    email VARCHAR(100),
    country VARCHAR(50)
);

INSERT INTO customers VALUES
    (1, 'Amit Sharma',   'amit@example.com',   'India'),
    (2, 'John Doe',      'john@example.org',   'USA'),
    (3, 'Annie Walker',  'annie@example.net',  'UK'),
    (4, 'Anita Desai',   'anita@example.com',  'India'),
    (5, 'Bob Smith',     'bob@example.com',    'USA');

SELECT customer_name FROM customers WHERE customer_name LIKE 'A%';

customer_name
-------------
Amit Sharma
Annie Walker
Anita Desai

Using the Underscore Wildcard

The _ wildcard matches exactly one character at its position.

SELECT customer_name FROM customers WHERE customer_name LIKE '_ob%';

customer_name
-------------
Bob Smith

Pattern 'A_' matches a two-character string that starts with A, not every string that starts with A.

Combining Percent and Underscore in a Single Pattern

You can combine % and _ to control fixed and flexible positions in one expression.

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'A_i%';

customer_name
-------------
Amit Sharma
Anita Desai

In A_i%, A must be first, _ matches one character, i must be third, and % allows any remaining characters. Both Amit Sharma and Anita Desai satisfy this shape. The trailing % also matches zero characters, so a hypothetical three-character name like Ani would match the same pattern: A at position 1, n at position 2, i at position 3, and % matching nothing.

Matching Literal Wildcards With ESCAPE

When stored data legitimately contains % or _, you need a way to match those characters as literals rather than as wildcards. The ESCAPE clause defines a single character that neutralizes the next wildcard in the pattern. This comes up most often when filtering product codes, percentage labels, file paths, or column names that contain underscores.

-- Match product codes that contain a literal underscore, for example "ABC_v2"
SELECT product_code FROM products WHERE product_code LIKE '%\_v2%' ESCAPE '\';

-- Match discount labels that contain a literal percent sign, for example "50% off"
SELECT label FROM offers WHERE label LIKE '%50\%%' ESCAPE '\';

The ESCAPE clause works in MySQL, PostgreSQL, SQL Server, and Snowflake with the same syntax. Any single character can serve as the escape, but a backslash is the most common convention. Without ESCAPE, every % and _ in your pattern is interpreted as a wildcard, which is a frequent source of bugs when patterns come from user input.

Match Records Starting With a String

This pattern returns names that begin with Jo.

SELECT customer_name FROM customers WHERE customer_name LIKE 'Jo%';

customer_name
-------------
John Doe

Match Records Ending With a String

This pattern returns names that end with a.

SELECT customer_name FROM customers WHERE customer_name LIKE '%a';

customer_name
-------------
Amit Sharma

Anita Desai ends with i, not a, so the %a pattern excludes it. Only Amit Sharma ends with the literal character a.

Match Records Containing a Substring

This pattern returns names that contain ann anywhere in the value.

SELECT customer_name FROM customers WHERE customer_name LIKE '%ann%';

customer_name
-------------
Annie Walker

NOT LIKE returns rows whose value does not match the given pattern.

Basic NOT LIKE Syntax

SELECT column_list FROM table_name WHERE column_name NOT LIKE pattern;

UPDATE table_name SET column_name = value WHERE column_name NOT LIKE pattern;

DELETE FROM table_name WHERE column_name NOT LIKE pattern;

SQL NOT LIKE Wildcard Examples

SELECT customer_name FROM customers WHERE customer_name NOT LIKE 'A%';

customer_name
-------------
John Doe
Bob Smith

SELECT customer_name FROM customers WHERE customer_name NOT LIKE '%a';

customer_name
-------------
John Doe
Annie Walker
Anita Desai
Bob Smith

Common Reasons SQL NOT LIKE Is Not Working

A common issue is using the wrong wildcard for the intended pattern.

-- Broken: _ matches one character, not many
SELECT customer_name FROM customers WHERE customer_name NOT LIKE 'A_';

-- Correct: % matches zero or more characters
SELECT customer_name FROM customers WHERE customer_name NOT LIKE 'A%';

Another issue is case sensitivity when the literal and collation rules do not align.

-- Broken in case-sensitive contexts
SELECT customer_name FROM customers WHERE customer_name NOT LIKE 'amit%';

-- Correct for PostgreSQL case-insensitive intent
SELECT customer_name FROM customers WHERE customer_name NOT ILIKE 'amit%';

Trailing spaces in stored values can also affect suffix checks.

-- Broken for values that end with a space
SELECT customer_name FROM customers WHERE customer_name NOT LIKE '%a';

-- Correct: trim trailing spaces first
SELECT customer_name FROM customers WHERE TRIM(customer_name) NOT LIKE '%a';

Using Multiple LIKE Conditions With OR

Use OR to match one of several patterns.

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'Am%'
   OR customer_name LIKE 'Jo%';

customer_name
-------------
Amit Sharma
John Doe

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'Am%'
   OR customer_name LIKE 'Jo%'
   OR customer_name LIKE 'B%';

customer_name
-------------
Amit Sharma
John Doe
Bob Smith

Why You Cannot Use LIKE With IN

-- Invalid: LIKE does not accept an IN list
SELECT customer_name
FROM customers
WHERE customer_name LIKE IN ('Am%', 'Jo%');

This query throws a syntax error in every major engine. IN checks exact equality against a list of values, and LIKE performs pattern matching, so they cannot be merged into one inline operator. The correct rewrite chains a separate LIKE predicate per pattern with OR.

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'Am%'
   OR customer_name LIKE 'Jo%';

Using REGEXP as an Alternative for Multiple Patterns

Regular expression matching can reduce repetition when you have many prefixes or shapes to match.

-- MySQL
SELECT customer_name FROM customers WHERE customer_name REGEXP '^(Am|Jo)';

-- PostgreSQL
SELECT customer_name FROM customers WHERE customer_name ~ '^(Am|Jo)';

-- PostgreSQL alternative using SIMILAR TO
SELECT customer_name FROM customers WHERE customer_name SIMILAR TO '(Am|Jo)%';

All three queries above return the same result against the dataset.

customer_name
-------------
Amit Sharma
John Doe

SQL Server has no native regex operator, so use OR-chained LIKE conditions.

Chaining NOT LIKE With AND

Use AND when each exclusion must be true for the row to remain in the result set.

SELECT customer_name
FROM customers
WHERE customer_name NOT LIKE 'A%'
  AND customer_name NOT LIKE 'B%';

customer_name
-------------
John Doe

AND is correct for exclusions. Switching to OR looks similar but produces the wrong result, because each row only needs to satisfy one of the two conditions for the whole expression to evaluate to true.

-- Wrong: OR returns every row
SELECT customer_name
FROM customers
WHERE customer_name NOT LIKE 'A%'
   OR customer_name NOT LIKE 'B%';

customer_name
-------------
Amit Sharma
John Doe
Annie Walker
Anita Desai
Bob Smith

Every row passes because a name that starts with A still does not start with B, so the second condition is true and the whole OR is satisfied. The same logic holds in reverse for any B-prefixed row. AND is the operator that requires every exclusion to hold simultaneously.

Using NOT REGEXP for Multiple Exclusion Patterns

These expressions exclude rows that start with either A or B.

-- MySQL
SELECT customer_name FROM customers WHERE customer_name NOT REGEXP '^(A|B)';

-- PostgreSQL
SELECT customer_name FROM customers WHERE customer_name !~ '^(A|B)';

-- PostgreSQL alternative
SELECT customer_name FROM customers WHERE customer_name NOT SIMILAR TO '(A|B)%';

customer_name
-------------
John Doe

LIKE ANY Syntax and Usage

PostgreSQL and Snowflake let you match a value against multiple patterns in one expression. You can read more about writing PostgreSQL queries if you want additional query patterns.

-- PostgreSQL
SELECT customer_name
FROM customers
WHERE customer_name LIKE ANY (ARRAY['Am%', 'Jo%']);

customer_name
-------------
Amit Sharma
John Doe

-- Snowflake
SELECT customer_name
FROM customers
WHERE customer_name LIKE ANY ('Am%', 'Jo%');

customer_name
-------------
Amit Sharma
John Doe

Snowflake also supports LIKE ALL, which requires the value to match every pattern in the list.

Equivalent Behavior in MySQL and SQL Server

MySQL and SQL Server do not support LIKE ANY, so use OR-chained LIKE predicates as the portable form.

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'Am%'
   OR customer_name LIKE 'Jo%';

customer_name
-------------
Amit Sharma
John Doe

MySQL LIKE Case Sensitivity

MySQL LIKE follows the column collation. The default on MySQL 8 is utf8mb4_0900_ai_ci, where ai means accent-insensitive and ci means case-insensitive, so LIKE 'amit%' matches Amit Sharma out of the box. Older MySQL 5.7 installations default to utf8mb4_general_ci, which is also case-insensitive. With a _cs or _bin collation, the same query returns no rows.

SELECT customer_name FROM customers WHERE customer_name LIKE BINARY 'amit%';

customer_name
-------------
(0 rows)

LIKE BINARY forces a byte-level comparison, so lowercase amit% does not match the capitalized value Amit Sharma.

To check what collation a table is actually using, run SHOW FULL COLUMNS:

SHOW FULL COLUMNS FROM customers;

+---------------+--------------+--------------------+------+-----+
| Field         | Type         | Collation          | Null | Key |
+---------------+--------------+--------------------+------+-----+
| customer_id   | int          | NULL               | NO   | PRI |
| customer_name | varchar(50)  | utf8mb4_0900_ai_ci | YES  |     |
| email         | varchar(100) | utf8mb4_0900_ai_ci | YES  |     |
| country       | varchar(50)  | utf8mb4_0900_ai_ci | YES  |     |
+---------------+--------------+--------------------+------+-----+

The Collation column shows the active collation per field. A _ci suffix means case-insensitive, and a _cs or _bin suffix means case-sensitive. Numeric columns return NULL because collation only applies to character types.

PostgreSQL ILIKE for Case-Insensitive Matching

PostgreSQL LIKE is case-sensitive by default, and ILIKE provides case-insensitive matching. The first query below returns zero rows because the literal amit% does not match the capitalized value. The second query returns Amit Sharma because ILIKE ignores case.

SELECT customer_name FROM customers WHERE customer_name LIKE 'amit%';

 customer_name
---------------
(0 rows)

SELECT customer_name FROM customers WHERE customer_name ILIKE 'amit%';

 customer_name
---------------
 Amit Sharma
(1 row)

SQL Server LIKE and Collation

In SQL Server, LIKE behavior follows the column or database collation, where _CI_ collations are case-insensitive and _CS_ collations are case-sensitive.

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'amit%' COLLATE Latin1_General_CS_AS;

customer_name
-------------
(0 rows)

The Latin1_General_CS_AS collation forces case-sensitive matching at the query level, which overrides the column default and causes amit% to miss Amit Sharma.

How Leading Wildcards Disable Index Scans

EXPLAIN SELECT customer_name FROM customers WHERE customer_name LIKE '%a';

                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customers  (cost=0.00..1.06 rows=1 width=39)
   Filter: ((customer_name)::text ~~ '%a'::text)

The Seq Scan line is the proof: with a leading % wildcard, the engine cannot use the B-tree index on customer_name and falls back to scanning every row. A B-tree index can serve LIKE 'A%' because the engine seeks into a known range, but LIKE '%a' has no left anchor, so the planner has nothing to seek to.

Alternatives When Performance Matters

When LIKE is too slow, you have three practical alternatives, each suited to a different access pattern.

Full-text search. Use this for natural-language matching across large text columns where you want word-level relevance, not literal-pattern matching. MySQL provides MATCH ... AGAINST against a FULLTEXT index, and PostgreSQL provides tsvector and tsquery against a GIN index.

-- MySQL: create a FULLTEXT index, then match against it
ALTER TABLE customers ADD FULLTEXT(customer_name);

SELECT customer_name FROM customers
WHERE MATCH(customer_name) AGAINST('Amit');

-- PostgreSQL: build a tsvector and match with tsquery
SELECT customer_name FROM customers
WHERE to_tsvector('english', customer_name) @@ to_tsquery('Amit');

SQL Server uses CONTAINS and FREETEXT against a full-text catalog, with similar semantics.

Trigram indexes for leading-wildcard matches. PostgreSQL’s pg_trgm extension creates a GIN index over three-character substrings, which lets the planner accelerate LIKE '%value%' and ILIKE '%value%' without changing the query syntax.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_customers_name_trgm
    ON customers USING gin (customer_name gin_trgm_ops);

After the index exists, queries like WHERE customer_name ILIKE '%mit%' use the trigram index automatically. As a rough order-of-magnitude reference, on a ten-million-row table a leading-wildcard LIKE against a non-trigram index typically takes one to three seconds, while the same query against a trigram index returns in tens of milliseconds.

Expression indexes on REVERSE(column) for suffix matches. A pattern like LIKE '%son' has no left anchor, but reversing the column and the pattern turns it into a prefix match, which a B-tree can serve.

-- PostgreSQL
CREATE INDEX idx_customers_name_reverse
    ON customers (REVERSE(customer_name));

SELECT customer_name FROM customers
WHERE REVERSE(customer_name) LIKE REVERSE('%son');

Pick full-text search for arbitrary substring matching at scale, pg_trgm when you want to keep LIKE syntax on PostgreSQL, and REVERSE() expression indexes when your patterns are predictable suffixes.

MySQL

MySQL LIKE is case-insensitive on default installations because MySQL 8 ships with utf8mb4_0900_ai_ci and MySQL 5.7 ships with utf8mb4_general_ci, both of which are case-insensitive. To force case-sensitive matching without altering the column, use LIKE BINARY. To check the active collation on any column, run SHOW FULL COLUMNS FROM table_name and read the Collation column.

PostgreSQL

PostgreSQL LIKE is case-sensitive regardless of database locale. Use ILIKE whenever the match should ignore case. A standard B-tree index does not accelerate ILIKE queries on its own, so for case-insensitive matching at scale build a pg_trgm GIN index using gin_trgm_ops as shown in the performance section.

SQL Server

SQL Server LIKE follows the column or database collation, and a fresh installation defaults to SQL_Latin1_General_CP1_CI_AS, which is case-insensitive. To override collation at query time without changing the column definition, append COLLATE Latin1_General_CS_AS (or any other case-sensitive collation) to the column reference in the WHERE clause. Confirm the collation in your target environment before deploying queries that depend on case behavior, because development, staging, and production databases often carry different defaults.

Snowflake

Snowflake LIKE is case-sensitive by default, which surprises developers migrating from MySQL where the default is the opposite. Use ILIKE for case-insensitive matching, or LIKE ANY and LIKE ALL for multi-pattern matching against an explicit list. Broad leading-wildcard patterns reduce micro-partition pruning on large tables, so the same performance guidance that applies to row-oriented engines applies to Snowflake as well.

What Is the Difference Between LIKE and NOT LIKE in SQL?

LIKE returns rows where a value matches a pattern, and NOT LIKE returns rows where a value does not match that pattern. Both operators are typically used in a WHERE clause. They use the same wildcard syntax, but invert the inclusion logic.

Why Is My NOT LIKE Query Not Working?

The most common causes are NULL handling, wildcard placement, and collation behavior. NOT LIKE does not return NULL rows unless you add OR column IS NULL. You should also verify whether % or _ matches your intended pattern shape, and confirm whether the column collation is case-sensitive.

How Do I Use LIKE With Multiple Values?

Chain separate LIKE predicates with OR, one predicate per pattern. For exclusion, chain NOT LIKE predicates with AND. PostgreSQL and Snowflake also support LIKE ANY for a compact list form.

What Does Percent (%) Do in SQL LIKE?

% matches zero or more characters. It can represent an empty suffix, a full word tail, or a longer string segment. For example, 'A%' matches values that begin with A.

What Does Underscore (_) Do in SQL LIKE?

_ matches exactly one character in a specific position. It does not match zero characters, and it does not match multiple characters. For example, '_ob%' matches Bob Smith.

Is SQL LIKE Case-Sensitive?

It depends on the engine and collation. PostgreSQL LIKE is case-sensitive, while ILIKE is case-insensitive. MySQL and SQL Server behavior follows collation settings.

What Is LIKE ANY, and Which Databases Support It?

LIKE ANY compares one value against a list of patterns in one expression. PostgreSQL supports it with an array form, and Snowflake supports it with a list form. MySQL and SQL Server do not support LIKE ANY.

Does a Leading Percent (%) Affect Query Performance?

Yes, it often does. A leading % pattern such as '%text' removes the left anchor that a B-tree index needs for a direct seek. Many engines then inspect rows more broadly, which increases work on large tables.

This article walked through LIKE and NOT LIKE syntax, % and _ wildcard behavior, multi-pattern matching with OR and AND, the LIKE IN misconception and its rewrite, LIKE ANY in PostgreSQL and Snowflake, engine-specific case sensitivity, and how leading wildcards affect index usage.

You can now write pattern filters for multiple inclusion and exclusion rules, choose between LIKE, ILIKE, and regex operators based on engine behavior, avoid the NULL trap in NOT LIKE, and estimate when a pattern can use an index.

For related SQL topics, continue with the linked tutorials on WHERE, SELECT, AND/OR, wildcards, and PostgreSQL queries, then validate your own examples on a managed database. DigitalOcean Managed Databases is a practical place to spin up PostgreSQL or MySQL instances for testing.