LIKE & Wildcards

LIKE matches text against a pattern rather than an exact value, which makes it useful for searching — finding names that start with a certain letter, emails from a certain domain, or product codes with a certain prefix.

The two wildcards

Wildcard	Meaning
%	Matches any sequence of characters, including zero characters
_	Matches exactly one character

Common patterns

Names starting with 'J'

SQL

SELECT first_name
FROM employees
WHERE first_name LIKE 'J%';

Emails ending in a specific domain

SQL

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

Anywhere in the string

SQL

SELECT product_name
FROM products
WHERE product_name LIKE '%wireless%';

Using _ to match exactly one character

SQL

SELECT product_code
FROM products
WHERE product_code LIKE 'A_1';
-- matches 'AB1', 'AX1', 'A11' — anything with exactly one
-- character between 'A' and '1'

NOT LIKE

Excluding a pattern

SQL

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

Case sensitivity depends on the database

Whether LIKE matching is case-sensitive depends on the database and the column’s collation. MySQL and SQL Server are commonly case-insensitive by default, while PostgreSQL’s LIKE is case-sensitive by default.

PostgreSQL's ILIKE

PostgreSQL provides a non-standard ILIKE operator that works exactly like LIKE but ignores case:

Case-insensitive matching in PostgreSQL

SQL

SELECT first_name
FROM employees
WHERE first_name ILIKE 'j%';  -- matches 'John', 'jane', 'JASON'...

Leading wildcards hurt performance

A pattern like LIKE '%something' — with a wildcard at the *start* — usually cannot use a standard B-tree index efficiently, because the database cannot narrow down where in the sorted index to start looking; it effectively has to scan every row. A pattern like LIKE 'something%', with the wildcard only at the end, can typically use an index just like a normal equality lookup. If you frequently need to search for text anywhere within a column, look into full-text search features instead of a leading-wildcard LIKE.