SQL LIKE Operator

The SQL LIKE operator filters text using a simple pattern. Use it when you know part of a value but not the whole thing: a name that starts with Al, an email address that ends with a company domain, or a product name that contains banana. Its two wildcards, % and _, stand in for unknown characters. Case sensitivity depends on the database.

This article is based on the pattern-matching lessons in Learn SQL. The course lets you run these queries against real tables in your browser.

SQL LIKE Syntax

Sometimes we don't have the luxury of knowing exactly what it is we need to query. Have you ever wanted to look up a song or a video but you only remember part of the name? SQL offers us an option for when we're in situations LIKE this.

The LIKE keyword compares a string expression on the left with a pattern on the right:

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

The LIKE operator expects a string value. Make sure the statement you are comparing against is wrapped in quotes, or SQL will think you're referring to a column! Without a wildcard, LIKE 'banana' behaves like an exact string comparison. A LIKE pattern must match the entire string, unlike most regular-expression searches. Add wildcards wherever unknown characters are allowed.

The LIKE keyword allows for the use of the % and _ wildcard operators:

Inside a quoted LIKE pattern, % is a wildcard. It has nothing to do with SQL's modulo operator.

Matching Text That Starts, Ends, or Contains a Value

The % operator will match zero or more characters. We can use this operator within our query string to find more than just exact matches, depending on where we place it.

Product starts with "banana":

SELECT * FROM products
WHERE product_name LIKE 'banana%';

This matches banana, banana bread, and bananas because % can match zero characters or many characters.

Product ends with "banana":

SELECT * FROM products
WHERE product_name LIKE '%banana';

Product contains "banana":

SELECT * FROM products
WHERE product_name LIKE '%banana%';

The last form is handy, but broad contains searches are often the most expensive. The SQL WHERE clause guide covers combining conditions and range filters.

The Underscore Wildcard

As discussed, the % wildcard operator matches zero or more characters. The _ wildcard operator, on the other hand, matches only a single character.

SELECT * FROM products
WHERE product_name LIKE '_oot';

The query above matches products like:

• boot
• root
• foot

SELECT * FROM products
WHERE product_name LIKE '__oot';

The query above matches products like:

• shoot
• groot

Each additional underscore requires another character, which is why '__oot' no longer matches root.

You can combine _ and %. To find five-character names beginning with Al:

SELECT name
FROM users
WHERE name LIKE 'Al___';

Al supplies two characters, and the three underscores require three more. By contrast, 'Al%' accepts Al followed by any number of characters, including none.

Matching Multiple LIKE Patterns

The portable approach is to use a separate LIKE expression for each pattern and join them with OR:

SELECT name
FROM users
WHERE name LIKE 'Al%'
   OR name LIKE 'Jo%';

Repeat the column and operator when the query needs to work across database systems. PostgreSQL also supports its dialect-specific LIKE ANY (array) syntax, but that form isn't portable. Group the OR expressions when you add other conditions:

SELECT name, status
FROM users
WHERE (name LIKE 'Al%' OR name LIKE 'Jo%')
  AND status = 'active';

Without the parentheses, AND takes precedence over OR, which changes the result.

NOT LIKE

NOT LIKE returns true when a non-NULL value doesn't match the pattern:

SELECT email
FROM users
WHERE email NOT LIKE '%@example.com';

This excludes email addresses ending in @example.com. It does not include rows where email is NULL. SQL comparisons with NULL, including LIKE and NOT LIKE, evaluate to unknown, and a WHERE clause keeps only true rows.

Include missing values explicitly if you need them:

SELECT email
FROM users
WHERE email NOT LIKE '%@example.com'
   OR email IS NULL;

This is SQL's three-valued logic: conditions can be true, false, or unknown. The SQL constraints guide covers NULL in more detail.

Escaping Literal Percent and Underscore Characters

Use the ESCAPE clause when % or _ should mean itself instead of a wildcard. Choose a one-character escape marker, then place it before the literal wildcard:

SELECT label
FROM discounts
WHERE label LIKE '100!%' ESCAPE '!';

This matches the literal value 100%. It doesn't match 100 percent.

For an underscore:

SELECT username
FROM users
WHERE username LIKE 'admin!_%' ESCAPE '!';

That pattern requires the literal prefix admin_, then allows any remaining characters. An explicit ESCAPE character is clearer and more portable than assuming every database treats backslashes the same way.

If a pattern comes from user input, escape the escape character first, then escape % and _. With ESCAPE '!', replace ! with !!, % with !%, and _ with !_ before adding any wildcards your application intends. Pass the finished pattern as a query parameter instead of concatenating untrusted strings into SQL.

SQL LIKE Case Sensitivity

LIKE case sensitivity isn't portable. Each database combines its own operator behavior with the active collation, which defines text-comparison rules.

Check the official PostgreSQL pattern-matching docs, SQLite expression docs, and MySQL pattern-matching docs for the database and version you're running.

If case-insensitive behavior must be consistent, choose an appropriate collation or define how your application converts stored data and search input to a consistent letter case. Unicode normalization and case folding are separate concerns, and both vary by database and locale. Applying LOWER() inside every query can hurt index use, so inspect the query plan and consider a matching expression index if your database supports one.

Index Use With LIKE

A prefix pattern such as LIKE 'banana%' gives the query optimizer, which chooses how to run the query, a known starting point. It can often use a B-tree index, the common index type for ordered values. A leading wildcard such as LIKE '%banana' or LIKE '%banana%' usually prevents a B-tree index from narrowing the search, so the database may scan many rows.

Index support still depends on the database, collation, index configuration, pattern, and query planner. PostgreSQL's index documentation, for example, documents B-tree support for constant patterns anchored at the beginning and its locale-related restrictions.

Use EXPLAIN on production-shaped data instead of guessing. The SQL indexes guide covers the tradeoff: indexes can speed up reads, but they consume storage and add work to writes.

LIKE vs. Regex vs. Full-Text Search

Use LIKE for simple string shapes: starts with, ends with, contains, or a fixed number of unknown characters. It's portable, readable, and deliberately limited.

Use a regular expression when the pattern needs alternation, repetition, character classes, or anchors beyond what % and _ express. Regex syntax isn't portable across database systems. PostgreSQL has POSIX operators, MySQL has REGEXP_LIKE(), and SQLite doesn't provide a working REGEXP implementation unless the application registers one.

Use full-text search when you need word matching, relevance ranking, language-aware tokenization, stemming, or large searchable documents. Tokenization splits text into searchable units; stemming groups related word forms such as "query" and "queries." A query such as body LIKE '%database index%' only matches a substring. PostgreSQL's full-text search, MySQL's FULLTEXT indexes, and SQLite's FTS5 extension are built for real search.