SQL LIKE: Pattern Matching With % and _

= only finds values that are exactly equal. LIKE finds values that fit a pattern. Every Gmail user:

SELECT first_name, last_name, email
FROM users
WHERE email LIKE '%@gmail.com'
ORDER BY last_name
LIMIT 5;

▶ Run this query in the SQL Playground

WHERE column LIKE 'pattern'
WHERE column NOT LIKE 'pattern'

Starts with

SELECT name, category
FROM products
WHERE name LIKE 'Pro%'
ORDER BY name;

▶ Run this query in the SQL Playground

Ends with

SELECT DISTINCT last_name
FROM users
WHERE last_name LIKE '%son'
ORDER BY last_name;

▶ Run this query in the SQL Playground

Contains

SELECT DISTINCT name
FROM products
WHERE name LIKE '%Set%'
ORDER BY name;

▶ Run this query in the SQL Playground

_ stands for exactly one character, so it fixes the position of what follows. First names whose second and third letters are "ar":

SELECT DISTINCT first_name
FROM users
WHERE first_name LIKE '_ar%'
ORDER BY first_name;

▶ Run this query in the SQL Playground

It is also handy for codes with a fixed layout. Every Playground SKU is SKU- plus six digits, so this finds SKU-000010 to SKU-000019:

SELECT sku, name
FROM products
WHERE sku LIKE 'SKU-00001_'
ORDER BY sku;

▶ Run this query in the SQL Playground

And a format check: every user's phone number fits the pattern +1-___-___-____:

SELECT COUNT(*) AS users,
       SUM(phone LIKE '+1-___-___-____') AS phones_in_format
FROM users;

▶ Run this query in the SQL Playground

NOT LIKE keeps the rows that do not match. Users on email providers other than Gmail, Yahoo and Hotmail:

SELECT COUNT(*) AS other_providers
FROM users
WHERE email NOT LIKE '%@gmail.com'
  AND email NOT LIKE '%@yahoo.com'
  AND email NOT LIKE '%@hotmail.com';

▶ Run this query in the SQL Playground

Each pattern needs its own LIKE or NOT LIKE, joined with AND or OR; LIKE does not take a list. Rows where the column is NULL match neither LIKE nor NOT LIKE.

It depends on the database:

In the Playground, LIKE ignores case while GLOB (which uses * and ? as wildcards) does not:

SELECT SUM(name LIKE '%set%') AS like_lowercase,
       SUM(name LIKE '%SET%') AS like_uppercase,
       SUM(name GLOB '*set*')  AS glob_lowercase,
       SUM(name GLOB '*Set*')  AS glob_capital_s
FROM products;

▶ Run this query in the SQL Playground

The portable way to search regardless of case is to lowercase both sides: WHERE LOWER(name) LIKE '%set%'.

To find a real percent sign or underscore, tell LIKE which character escapes the next one. The sample values below are written into the query:

WITH promos(code) AS (
  VALUES ('SAVE10%'), ('SAVE10'), ('NEW_USER'), ('NEWUSER')
)
SELECT code,
       code LIKE '%!%%' ESCAPE '!' AS has_percent_sign,
       code LIKE '%!_%' ESCAPE '!' AS has_underscore,
       code LIKE '%_%'             AS unescaped_underscore
FROM promos;

▶ Run this query in the SQL Playground

Unescaped, '%_%' means "at least one character", so it matches every code. With ! as the escape character, !_ means a real underscore. Any character not used in the data can be the escape character.

LIKE works on text. Dates in the Playground are text in YYYY-MM-DD form, so a prefix pattern finds a month:

SELECT COUNT(*) AS orders_in_dec_2024
FROM orders
WHERE order_date LIKE '2024-12-%';

▶ Run this query in the SQL Playground

It works, but a range (order_date >= '2024-12-01' AND order_date < '2025-01-01') is the better habit: it works for real date types in every database and can use an index. For numbers, compare numerically with BETWEEN or >; CAST(price AS TEXT) LIKE '1%' runs but is rarely what you mean.

For "contains" searches over large text, databases offer full-text search: FTS5 in SQLite, tsvector in PostgreSQL, FULLTEXT indexes in MySQL and SQL Server.

Five errors and how to avoid them.

SELECT * FROM users
WHERE last_name = 'A%';

SELECT * FROM users
WHERE last_name LIKE 'A%';

WHERE name LIKE 'Set%'

WHERE name LIKE '%Set%'

WHERE name LIKE '%set%'  -- 0 rows in PostgreSQL

WHERE LOWER(name) LIKE '%set%'

WHERE email LIKE ('%@gmail.com', '%@yahoo.com')

WHERE email LIKE '%@gmail.com'
   OR email LIKE '%@yahoo.com'

WHERE name LIKE '%phone%'   -- millions of rows

WHERE name LIKE 'phone%'
-- or a full-text index for word searches

5 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List the products whose name ends with "Kit". Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name, category
   FROM products
   WHERE name LIKE '%Kit'
   ORDER BY name;

   ▶ Run this query in the SQL Playground

   The % goes before the fixed ending.

2. 2Easy Count the employees whose title contains "Engineer". Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS engineers
   FROM employees
   WHERE title LIKE '%Engineer%';

   ▶ Run this query in the SQL Playground

   Contains needs % on both sides; it also matches "Engineering".

3. 3Medium List the distinct first names that are exactly four letters long. Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT DISTINCT first_name
   FROM users
   WHERE first_name LIKE '____'
   ORDER BY first_name
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   Four underscores mean exactly four characters.

4. 4Medium Count the users per email provider for Gmail, Yahoo and everyone else. Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT CASE WHEN email LIKE '%@gmail.com' THEN 'Gmail'
               WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'
               ELSE 'Other'
          END AS provider,
          COUNT(*) AS users
   FROM users
   GROUP BY provider
   ORDER BY users DESC;

   ▶ Run this query in the SQL Playground

   LIKE works inside CASE as well as in WHERE.

5. 5Medium Count the reviews whose comment mentions "price" in any capitalisation. Table: reviews

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS mentions_price
   FROM reviews
   WHERE LOWER(comment) LIKE '%price%';

   ▶ Run this query in the SQL Playground

   LOWER makes the search work the same way in every database.