SQL tutorial · Intermediate

Pattern Matching (LIKE & REGEXP)

Intermediate

⏱️ 10 mins read

Matches against employees (Topic 1) and customers (Topic 6) — same two tables as Topic 12, now with wildcards instead of functions.

What You'll Learn

Pattern matching allows you to search for data that follows a specific format rather than an exact value. The LIKE operator uses wildcards: '%' (matches zero or more characters) and '_' (matches exactly one character). For advanced validation, databases support REGEXP (Regular Expressions) to find patterns like 'starts with a number' or 'contains exactly 3 digits'.

Syntax

SELECT * FROM table WHERE col LIKE 'A%'; -- Starts with A
SELECT * FROM table WHERE col LIKE '_B%'; -- Second letter is B
SELECT * FROM table WHERE col REGEXP '^[0-9]'; -- Starts with a digit

Example

-- Emails starting with 's'
SELECT name, email FROM customers WHERE email LIKE 's%';   -- Sofia

-- Names with 'a' as the second letter
SELECT name FROM employees WHERE name LIKE '_a%';

-- Regex: names containing exactly 5 letters
SELECT name FROM customers WHERE name REGEXP '^[A-Za-z]{5}$';

Beyond the Basics

Data in play — reference tables for this topic

employees (from Topic 1) — name column5 rows

name
Ada
Grace
Alan
Edsger
Barbara

customers (from Topic 6) — name column5 rows

name
Karl
Ines
Omar
Sofia
Maike

'%pat%' is a full scan — Topic 3's sargable rule, pattern edition

LIKE can use a B-tree index only when the pattern's prefix is a literal: name LIKE 'A%' seeks straight to the A's. The moment a wildcard leads — name LIKE '%son%' — the engine must check every row, because a match could hide anywhere. Same disease as YEAR(hire_date) and LOWER(email) from Topics 3 and 12.

-- Index-friendly: literal prefix, engine seeks the range
SELECT name FROM employees WHERE name LIKE 'A%';   -- Ada, Alan

-- Index-dead: leading wildcard, every row must be tested
SELECT name FROM employees WHERE name LIKE '%a%';
-- On five rows the difference is invisible; on 50 million it is the outage

Lead with literals whenever the business question allows — 'starts with' is indexable, 'contains' never is.

LIKE has no 'or' — REGEXP fills the gap, but only in some engines

LIKE patterns have no alternation: 'ends with a or e' forces two LIKEs glued with OR. Regular expressions solve it with [ae]$ — but regex support is dialect roulette: MySQL has REGEXP, PostgreSQL uses ~ or REGEXP_LIKE, and SQL Server has no native regex at all (LIKE only, until recent preview features).

-- Portable but clunky:
SELECT name FROM customers
WHERE name LIKE '%a' OR name LIKE '%e';   -- Ines, Sofia, Maike

-- Expressive, MySQL syntax:
SELECT name FROM customers WHERE name REGEXP '[ae]$';

-- PostgreSQL syntax:
SELECT name FROM customers WHERE name ~ '[ae]$';

LIKE travels everywhere but only does prefixes and suffixes; regex is powerful but locks the query to a dialect — know which trade you are making before writing it into production.

Searching for a literal % or _: the ESCAPE clause

% and _ are wildcards in every LIKE pattern — so searching for text that contains them literally needs an escape character. Underscores are everywhere in real schemas (order_items, first_name), which makes this the rare interview question that mirrors daily work.

-- Table name stored in a config/audit table, e.g. 'order_items':
SELECT * FROM audit_log
WHERE object_name LIKE '%order\_items%' ESCAPE '\';

-- Without ESCAPE, 'order_items' matches 'orderXitems' too —
-- the _ wildcard silently eats the underscore you were looking for

Underscore is the wildcard people forget: LIKE '%user_name%' does NOT mean 'user_name' — declare ESCAPE and be explicit.

Common Mistakes

Using leading wildcards like '%word' on large tables. This forces a full table scan because the database cannot use an index. Also, forgetting that LIKE is case-sensitive in PostgreSQL (use ILIKE) but case-insensitive in MySQL by default.

Interview Tips

Explain SARGability: 'pattern%' is index-friendly, but '%pattern' is not. Mention the ESCAPE clause if you need to search for literal '%' or '_' characters: WHERE discount LIKE '10\%%' ESCAPE '\'.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. Which LIKE pattern can use a B-tree index?

2. name LIKE '_a%' matches which ShopCo employees?

3. Is LIKE case-sensitive?

4. 'Names ending in a or e' — which tool handles the OR natively?

5. How do you search for a literal underscore in LIKE?

6. Why is '%pattern%' called the expensive LIKE?

Practice

Return the names of employees whose name starts with the letter A.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Is LIKE case-sensitive?

Depends on the engine and collation: PostgreSQL's LIKE is case-sensitive (use ILIKE or LOWER()); MySQL is case-insensitive under its default collations; SQL Server follows the column's collation. Never assume — test with one row you know has mixed case.

How do I make '%contains%' searches fast on a huge table?

You cannot with a plain B-tree index — leading wildcards are unindexable by design. Options: full-text search (documents, articles), trigram indexes like pg_trgm in PostgreSQL (arbitrary substrings), or external search engines. Knowing WHEN to say 'LIKE is the wrong tool here' is the senior answer.

String Functions

Date Functions

Back to Full Roadmap