🔎 FilteringbeginnerCommonly used

How to use LIKE for pattern matching in SQL

Filter rows based on partial string matches using wildcards.

Starts with a stringAll databases

SELECT name, email
FROM users
WHERE name LIKE 'Al%';

% matches any sequence of characters. Returns names starting with "Al" (Alice, Albert, etc).

Ends with a stringAll databases

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

Returns rows where email ends with @gmail.com.

Contains a stringAll databases

SELECT name, email
FROM users
WHERE name LIKE '%smith%';

% on both sides matches anywhere in the string.

Leading wildcard (%smith) prevents index use — slow on large tables. Use full-text search instead.

Single character wildcardAll databases

SELECT name
FROM users
WHERE name LIKE '_ob';

_ matches exactly one character. Matches "Bob", "Rob", "Job".

Case-insensitive match (PostgreSQL)PostgreSQL

SELECT name, email
FROM users
WHERE name ILIKE '%alice%';

ILIKE is PostgreSQL's case-insensitive version of LIKE.

Case-insensitive match (MySQL / SQLite)MySQL

SELECT name, email
FROM users
WHERE LOWER(name) LIKE '%alice%';

Use LOWER() to normalize case before matching.

NOT LIKEAll databases

SELECT name, email
FROM users
WHERE email NOT LIKE '%@spam.com';

Returns rows that do not match the pattern.

Common Mistakes