Case-Insensitive Email Matching

SQLPattern matchingJuniorMidUpdated Nov 10, 2026

Find every user whose email ends in @example.com. Some addresses were typed with capitals, like Sam@Example.com.

Schema

CREATE TABLE users (
  id     BIGINT PRIMARY KEY,
  email  TEXT NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (c)

SELECT id, email
FROM users
WHERE email ILIKE '%@example.com';

ILIKE is the case-insensitive version of LIKE in Postgres, and the leading % matches any prefix, so (c) finds Sam@Example.com too. LIKE (a) is case-sensitive in Postgres and misses the capitalized addresses. (b) compares against the literal text '%@example.com', because = does no pattern matching. (d) puts the wildcard at the wrong end and matches emails that start with @example.com.

Going Deeper

A leading % means a plain B-tree index on email can't help, so this is a full scan on a big table. For case-insensitive lookups of a whole address, index lower(email) and compare lower(email) = lower($1). For fast '%...' searches, a pg_trgm trigram index supports both LIKE and ILIKE. In LIKE patterns, _ matches any single character, so escape it when you mean a literal underscore.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

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

Option (b)

SELECT id, email
FROM users
WHERE email = '%@example.com';

Option (d)

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