When LIKE is not enough. Write a real pattern and watch it match — character by character — against every row, with the equivalent LIKE shown alongside so you can see exactly what regex buys you.
Overview
The idea in brief
A regular expression describes a shape that text may take, rather than the text itself. SQL's LIKE offers exactly two wildcards; regex offers character classes, quantifiers, anchors, alternation and grouping — enough to validate, extract and clean data directly in the database.
Regular Expressions in SQL: Pattern Matching That Actually Fits
LIKE handles "starts with". Regex handles "a valid email address".
Every engine spells it differently
This is the part that makes regex in SQL awkward: there is no portable syntax.
Engine
Match operator
Extract
Replace
PostgreSQL
~ (case-sensitive), ~*
REGEXP_SUBSTR, substring(x from '...')
REGEXP_REPLACE
MySQL 8+
REGEXP / RLIKE
REGEXP_SUBSTR
REGEXP_REPLACE
Oracle
REGEXP_LIKE(x, '...')
REGEXP_SUBSTR
REGEXP_REPLACE
SQL Server
None built in
—
— (CLR or LIKE tricks)
SQLite
REGEXP if the host registers it
—
—
SQL Server's absence is worth planning around: patterns there are usually expressed with LIKE plus character-class brackets (LIKE '[0-9][0-9][0-9]%'), or the work moves to the application.
Note also that PostgreSQL's ~ is POSIX-flavoured while MySQL 8 uses ICU, so a few advanced constructs differ even between two engines that both "have regex".
LIKE vs REGEXP
Try the email-validation pattern in this lab and read the LIKE equivalent underneath it. LIKE can express "contains an @" and little more; it cannot say "one or more of these characters", "exactly four digits", or "either gmail or yahoo". Those are quantifiers, character classes and alternation — the things LIKE simply does not have.
One more difference that catches people: LIKE 'abc' must match the whole string, whereas most SQL regex operators match if the pattern is found anywhere in it. That is why anchors ^ and $ matter so much — toggle them in this lab and watch the match count change.
The performance warning
A regular expression is applied row by row and cannot use an ordinary B-tree index. WHERE email ~ 'gmail' on ten million rows is a full table scan, every time.
Four ways to keep that from being your query:
Narrow first. Combine the regex with an indexable condition — WHERE created_at > ... AND email ~ '...' — so the pattern is only applied to rows that survived the cheap filter.
Prefer LIKE when it suffices. A prefix LIKE 'abc%'can use an index; regex cannot.
Index the expression. If you always extract the same thing, store or index it: an expression index on substring(email from '@(.*)$'), or a generated column holding the domain.
Use the right tool for text search. Word matching, ranking and stemming are what full-text search is for — PostgreSQL's tsvector with a GIN index, or a dedicated search engine. Regex is not a search engine.
There is also a correctness-flavoured performance risk: certain nested-quantifier patterns can take exponential time on adversarial input (catastrophic backtracking). Never apply a user-supplied regular expression to a large table.
When LIKE is not enough
LIKE handles simple patterns: 'a%' for a prefix, '%son' for a suffix, '_at' for exactly one character then "at". Two wildcards, no repetition, no alternatives, no character classes.
Regular expressions handle everything else — validating an email shape, extracting the digits out of a reference code, matching one of several spellings, finding rows where a field contains two consecutive capital letters.
-- LIKE: does it start with 'A'?
SELECT * FROM products WHERE name LIKE 'A%';
-- Regex: is it a plausible UK postcode?
SELECT * FROM addresses
WHERE postcode ~ '^[A-Z]{1,2}[0-9][A-Z0-9]? [0-9][A-Z]{2}$';
The building blocks are the same as in every other language:
Pattern
Meaning
^$
Start and end of the string
.
Any single character
*+?
Zero or more, one or more, zero or one
{2,4}
Between two and four times
[abc][^abc]
One of these; anything but these
[0-9][A-Za-z]
Digit; letter
|
Either side — alternation
( )
A group, capturable for extraction
\d\w\s
Digit, word character, whitespace (engine-dependent)
Three things they are actually used for
Validation — finding rows that do not match an expected shape:
SELECT id, email FROM users
WHERE email !~ '^[^@\s]+@[^@\s]+\.[a-z]{2,}$'; -- suspicious addresses
(No regex fully validates an email address; the aim is to catch obvious rubbish, not to be a specification.)
Extraction — pulling a piece out of a larger string:
-- the numeric part of a reference like 'INV-2026-00417'
SELECT REGEXP_REPLACE(reference, '^[A-Z]+-[0-9]{4}-', '') AS number FROM invoices;
-- the domain from an email, using a capture group
SELECT substring(email from '@(.*)$') AS domain FROM users;
Cleaning — normalising messy input:
-- strip everything that is not a digit from a phone number
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') FROM contacts;
That 'g' flag — replace every occurrence, not just the first — is a frequent omission in PostgreSQL, where the default is to replace only once.
Exploration guide
Start with ^A. The anchor forces the match to the start; matched text is highlighted inside each value.
Remove the ^. Suddenly any row containing an "A" anywhere matches. Anchors are the difference between "starts with" and "contains".
Run the email pattern. Malformed addresses in the data fail it — this is validation LIKE cannot perform.
Try the code pattern ^[A-Z]{2}[0-9]{4}$ with case-insensitivity on and off, and watch which rows drop out.
Type a deliberately broken pattern such as [a-. The error is reported rather than silently matching nothing.
What to remember
Reach for regex when the shape of the text matters and LIKE cannot describe it — validation, extraction, messy-data cleanup. Reach for LIKE when a prefix will do, because it can use an index and regex usually cannot. This lab runs your pattern through a real regex engine, so what matches here is what will match in your database.
Writing patterns that survive review
Anchor them. Without ^ and $, a pattern matches anywhere in the string, which is usually not what a validation rule means.
Escape properly. A literal dot is \.; unescaped it matches any character, so '^[0-9]+.[0-9]+$' accepts "12x34".
Watch case sensitivity. PostgreSQL's ~ is case-sensitive, ~* is not; MySQL's REGEXP follows the column collation.
Prefer explicit classes.[0-9] is unambiguous across engines; \d is not always available and may include non-ASCII digits under Unicode rules.
Test against real data, including the rows you expect to fail. A pattern that matches everything is easy to write and hard to notice.
Comment non-trivial patterns in the schema or the migration. A postcode regex is unreadable six months later, including to the person who wrote it.
Pattern matching, and what it costs the index
SQL has two pattern languages of very different power, and a third that is not always there at all. Which one you reach for decides whether the query can use an index, which matters far more than the syntax.
query.sqlSQLite
-- LIKE is the portable one. Two wildcards, and that is the whole
-- language: % matches any run of characters, _ matches exactly one.
SELECT 'city LIKE L%' AS pattern, group_concat(city) AS matches
FROM customers WHERE city LIKE 'L%'
UNION ALL
SELECT 'city LIKE %n', group_concat(city) FROM customers WHERE city LIKE '%n'
UNION ALL
SELECT 'city LIKE _____', group_concat(city) FROM customers WHERE city LIKE '_____'
UNION ALL
SELECT 'city LIKE %o%', group_concat(city) FROM customers WHERE city LIKE '%o%';
-- GLOB is the same idea with shell syntax, and unlike LIKE it is
-- case-sensitive in SQLite.
SELECT 'GLOB L*' AS pattern, group_concat(city) AS matches
FROM customers WHERE city GLOB 'L*'
UNION ALL
SELECT 'GLOB l*', group_concat(city) FROM customers WHERE city GLOB 'l*'
UNION ALL
SELECT 'LIKE l%', group_concat(city) FROM customers WHERE city LIKE 'l%';
-- Note the last two rows: GLOB finds nothing for a lowercase l, while
-- LIKE returns both cities. That difference has surprised people into
-- shipping a filter that works in testing and misses half the data.
--
-- REGEXP is the powerful one, and SQLite does not implement it -- the
-- keyword parses and the function is not defined unless the host
-- program supplies it. Postgres has ~ and MySQL has REGEXP built in.
-- Portable code does not assume it exists.
--
-- AND HERE IS THE PART THAT MATTERS. An index on a text column is
-- sorted by that text, so a pattern anchored at the start ought to be
-- answerable as a range scan. Whether it actually is depends on a
-- detail that is easy to miss.
CREATE INDEX idx_city ON customers(city);
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city = 'London';
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city LIKE 'L%';
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city GLOB 'L*';
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city LIKE '%n';
-- Read those four plans. Equality searches the index, as expected. So
-- does GLOB 'L*'. But LIKE 'L%' -- the same prefix, in the operator
-- everybody actually uses -- falls back to a full SCAN.
--
-- The reason is collation. LIKE is case-insensitive by default, and the
-- index is sorted case-sensitively, so the sorted order does not group
-- the rows LIKE considers equal and a range scan would give the wrong
-- answer. GLOB is case-sensitive, so its range scan is valid.
--
-- Two ways to get the optimisation back. Either make LIKE
-- case-sensitive:
PRAGMA case_sensitive_like = ON;
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city LIKE 'L%';
-- or build the index with the collation LIKE uses:
PRAGMA case_sensitive_like = OFF;
CREATE INDEX idx_city_nocase ON customers(city COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city LIKE 'L%';
-- Both now SEARCH. The general rule behind it is worth carrying to
-- other engines: an index can only serve a comparison that agrees with
-- the index's own collation, which is why a case-insensitive search on
-- a case-sensitive index quietly scans.
--
-- And the last plan in the first group is the one no collation fixes.
-- A leading wildcard means every row must be examined, for the same
-- reason a phone book cannot find everyone whose surname ends in
-- "son". If you need that search, the answer is a full-text or trigram
-- index, not a bigger machine.
Result
Questions people ask
Is REGEXP case-sensitive? Depends on the engine and collation. PostgreSQL: ~ yes, ~* no. MySQL: follows the column's collation, so often not.
Can I use a regex in a CHECK constraint? Yes, and it is a legitimate way to enforce a format — a product code shape, for instance. Keep it simple, because it runs on every insert and update.
Why does my backslash not work? Because it is being consumed by the string literal before the regex engine sees it. PostgreSQL's E'...' strings and standard strings differ here; doubling the backslash or using a different quoting style usually fixes it.
Should I validate emails with a regex? For catching typos, a loose pattern is fine. For genuine validation, send a confirmation message — that is the only test that proves an address exists.
Can I extract several matches from one string? PostgreSQL's REGEXP_MATCHES(x, pattern, 'g') returns a row per match; MySQL 8 has REGEXP_SUBSTR with an occurrence argument. Neither is portable.
Is regex slower than LIKE? Generally yes, and more importantly it cannot use an index where a prefix LIKE can. Use the simpler tool when it does the job.
Recap in one screen
Use LIKE for simple prefixes and suffixes; use regex for shapes, alternatives and repetition.
The syntax and the functions differ by engine, and SQL Server has no built-in support.
The three real jobs are validation, extraction and cleaning.
Regex cannot use a B-tree index — combine it with an indexable filter, or index the extracted expression.
Anchor patterns with ^ and $, escape literal dots, and never run user-supplied patterns over a large table.
Predict, then reveal
About to run: Run the email pattern. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Recall check
0 of 4
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What is meant by “Anchor them” here?
Without ^ and $ , a pattern matches anywhere in the string, which is usually not what a validation rule means.
What is meant by “Escape properly” here?
A literal dot is \. ; unescaped it matches any character, so '^[0-9]+.[0-9]+$' accepts "12x34".
What is meant by “Watch case sensitivity” here?
PostgreSQL's ~ is case-sensitive, ~* is not; MySQL's REGEXP follows the column collation.
What is meant by “Prefer explicit classes” here?
[0-9] is unambiguous across engines; \d is not always available and may include non-ASCII digits under Unicode rules.
Cheat sheet
Regular Expressions in SQL
When LIKE is not enough. Write a real pattern and watch it match — character by character — against every row, with the equivalent LIKE shown alongside so you can see exactly what regex buys you.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.