Pattern Matching (LIKE & REGEXP) — SQL Tutorial with Examples

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 column
-- Ada, Grace, Alan, Edsger, Barbara

-- customers (from Topic 6) — name column
-- 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.