SQL LIKE: prefix, suffix, and substring filters

SQL LIKE filters rows by matching a string column against a pattern with SQL wildcards. The % wildcard matches zero or more characters, and _ matches exactly one character. Use it for SQL starts with filters ('prefix%'), suffix filters ('%suffix'), and substring filters ('%term%').

SQL LIKE Example For Prefix Matching

Output:

+-------+----------------+
| name  | email          |
+-------+----------------+
| Alice | alice@corp.com |
| Alina | alina@corp.com |
+-------+----------------+
2 row(s)

How LIKE ‘Al%’ Filters Rows

1. CREATE TABLE and INSERT populate customers with four rows.
2. name LIKE 'Al%' keeps names that start with Al; % matches the remaining characters.
3. With a normal B-tree index on name, a prefix pattern like 'Al%' can use an index range scan.

What Is the SQL LIKE Operator?

SQL LIKE compares a string column to a pattern. LIKE matches the whole string (not a substring) unless you add % on one or both sides. With no wildcards, LIKE 'text' behaves like = 'text'. If the column is NULL, LIKE and NOT LIKE evaluate to unknown and the row is filtered out by WHERE.

Wildcard Patterns: %, _, and ESCAPE

% and _ cover most filtering needs. In a SQL WHERE wildcard pattern, mix fixed text with % and _ to express prefix/suffix/substring matches:

To search for a literal % or _, define an escape character with the ESCAPE clause:

SELECT label FROM metrics
WHERE label LIKE '%100\%%' ESCAPE '\';

The \ is declared as the escape character, so \% matches a real % rather than acting as a wildcard.

SQL Server and MS Access add a [charlist] wildcard that matches one character from a set or range, such as '[A-C]%'. It is not available in SQLite, MySQL, or PostgreSQL.

SQL NOT LIKE: Exclude Matching Rows

NOT LIKE inverts the match. A SQL query NOT LIKE returns every row where the pattern does not match:

SELECT name FROM customers
WHERE email NOT LIKE '%corp%';

This returns Bob and Carol, the rows whose email does not contain corp. Rows where the column is NULL are excluded from both LIKE and NOT LIKE results. To include NULLs, add an explicit OR column IS NULL.

SQL LIKE With Multiple Values

LIKE tests one pattern at a time. To match several patterns, chain them with OR:

SELECT name FROM customers
WHERE name LIKE 'A%' OR name LIKE 'C%';

This returns Alice, Alina, and Carol, the names starting with A or C. Wrap the conditions in parentheses when other filters share the WHERE clause, so the OR group is evaluated as a unit. IN does not support wildcards, so name IN ('A%', 'C%') matches the literal strings A% and C%, not prefixes. For many patterns, store them in a table and join against it, or switch to full-text search.

When % and _ Aren’t Enough: Regex

% and _ cannot express alternations, character classes, or repetition. When the pattern needs that power, switch to the engine’s regex operator: REGEXP_LIKE(col, 'pattern') in Oracle, col REGEXP 'pattern' (or RLIKE) in MySQL, and col ~ 'pattern' or SIMILAR TO in PostgreSQL. SQLite exposes REGEXP only when the host registers a regex function. Regex never uses a normal index, so keep LIKE 'prefix%' for plain prefix filters and reserve regex for genuinely complex patterns.

Common Mistakes With SQL LIKE

Mistake: Assuming LIKE 'term' matches substrings.

Wrong:

WHERE name LIKE 'li'

Right:

WHERE name LIKE '%li%'

Why: LIKE matches the entire string. Without % on both sides, the pattern requires an exact match against li.

Mistake: Searching for a literal % without ESCAPE.

Wrong:

WHERE discount LIKE '100%'

Right:

WHERE discount LIKE '100\%' ESCAPE '\'

Why: Without ESCAPE, % acts as a wildcard, matching 100, 1000, 100USD, and anything else starting with 100.

LIKE Performance: Prefix vs Leading Wildcard

With a normal B-tree index, LIKE 'abc%' can use an index range scan. Patterns that start with % (like '%abc' or '%abc%') prevent an index seek, so the engine typically scans many more rows.

For large-scale substring search, use full-text search (FTS) instead of LIKE '%term%'.

Case Sensitivity by Dialect

LIKE case behavior varies by database engine:

SQLite treats LIKE 'al%' and LIKE 'Al%' the same for ASCII. PRAGMA case_sensitive_like = ON switches to exact case matching. PostgreSQL’s ILIKE is an extension not part of the SQL standard.

Wrapping columns in LOWER() works across dialects but prevents index use. Prefer a case-insensitive collation or dialect-specific operators when performance matters.

FAQ

Is SQL LIKE case-sensitive?

It depends on the database. PostgreSQL is case-sensitive and offers ILIKE for insensitive matching, MySQL follows the column collation (often case-insensitive), and SQLite is case-insensitive for ASCII letters by default. For portable case-insensitive matching, compare LOWER(column) to a lowercase pattern, at the cost of index use.

How do I use SQL LIKE with multiple values?

Chain patterns with OR, as in WHERE col LIKE 'a%' OR col LIKE 'b%'. IN does not support wildcards, so IN ('a%', 'b%') matches those literal strings rather than prefixes. For many patterns, store them in a table and join against it.

What is the difference between LIKE and ILIKE?

LIKE matches according to the database’s case rules. ILIKE is a PostgreSQL extension that always matches case-insensitively. It is not part of the SQL standard, so other engines rely on a case-insensitive collation or LOWER() instead.

What does the % wildcard match in SQL LIKE?

% matches zero or more characters, so 'A%' matches any string that starts with A. _ matches exactly one character. Combine them for prefix ('A%'), suffix ('%n'), and substring ('%li%') patterns.