Pattern Matching
Beyond basic LIKE, SQL databases offer advanced pattern matching with regular expressions. MySQL uses REGEXP/RLIKE. PostgreSQL uses ~ (case-sensitive) and ~* (case-insensitive). For high-performance text search, use Full-Text Search indexes.
Pattern Matching Options
- LIKE — simple wildcards (% and _), supported everywhere
- REGEXP / RLIKE (MySQL) — full regular expressions
- ~ and ~* (PostgreSQL) — POSIX regex operators
- SIMILAR TO (PostgreSQL) — SQL standard regex (limited)
- Full-Text Search — for production text search at scale
- Regular expressions cost more than LIKE — avoid on millions of rows without indexes
Pattern Matching Examples
-- LIKE (standard, all databases)
SELECT name FROM users WHERE email LIKE '%@gmail.com';
-- MySQL REGEXP: email with standard format
SELECT name, email FROM users
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';
-- PostgreSQL ~ (case-sensitive regex)
SELECT name FROM products WHERE name ~ '^[A-Z]';
-- Starts with uppercase letter
-- PostgreSQL ~* (case-insensitive regex)
SELECT name FROM products WHERE name ~* 'phone';
-- Matches: Phone, iPhone, smartphone
-- PostgreSQL !~ (does NOT match regex)
SELECT name FROM products WHERE name !~ '\d';
-- Names without digits
-- Full-Text Search (MySQL):
SELECT name, MATCH(name) AGAINST('laptop phone' IN BOOLEAN MODE) AS score
FROM products
WHERE MATCH(name) AGAINST('laptop phone' IN BOOLEAN MODE);
-- Full-Text Search (PostgreSQL):
SELECT name, ts_rank(to_tsvector(name), to_tsquery('laptop | phone')) AS rank
FROM products
WHERE to_tsvector(name) @@ to_tsquery('laptop | phone')
ORDER BY rank DESC;Quick Quiz
Tip
Tip
Practice Pattern Matching in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Selectors target HTML elements for styling
Common Mistake
Warning
A common mistake with Pattern Matching is skipping edge case testing — empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task — (1) Write a working example of Pattern Matching from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Beyond basic LIKE, SQL databases offer advanced pattern matching with regular expressions.
- LIKE — simple wildcards (% and _), supported everywhere
- REGEXP / RLIKE (MySQL) — full regular expressions
- ~ and ~* (PostgreSQL) — POSIX regex operators