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 | [email protected] |
| Alina | [email protected] |
+-------+----------------+
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 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 — anyone 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.

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.