Pattern Matching (LIKE & REGEXP)
Intermediate⏱️ 10 mins read
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 digitExample
-- Find all gmail users
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Find names with 'a' as the second letter
SELECT name FROM employees WHERE name LIKE '_a%';
-- Regex: names containing exactly 5 letters
SELECT name FROM students WHERE name REGEXP '^[A-Za-z]{5}$';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 '\'.
Practice
Find all products whose codes start with 'PROD-', followed by exactly 3 digits, and end with either '-S' or '-M'.