Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 125 ยท Functions and Expressions

Regular Expressions in SQL

Match, validate, extract and clean text in SQL with regular expressions. Learn REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_COUNT and PostgreSQL's ~ operators.

LIKE is good at simple questions: does a name start with "Chef", does an email end with .com? It has only two wildcards, so it can't express "exactly five digits", "one or more letters followed by a dash" or "an email address of the usual shape". For those you need a regular expression (regex): a compact pattern language for describing text.

Regular expressions in SQL do four jobs. They filter rows (WHERE email matches a pattern), validate (a CHECK that a phone number is well-formed), extract (pull the domain out of an email) and clean (strip everything that isn't a digit). This lesson teaches the pattern syntax once, then shows how each major database exposes it, and runs real examples in your browser.

What runs here. SQLite understands the REGEXP keyword but ships no function behind it, so x REGEXP 'pattern' fails in the SQL editor on this site. To let you experiment, the live examples below use Python's built-in sqlite3 module and register a regexp function backed by Python's re. That is also how real SQLite programs get regex support. The PostgreSQL, MySQL, Oracle and SQL Server statements are read-only.

Pattern syntax in one table

The core of the language is the same in every database. Learn these pieces and you can read almost any pattern:

Piece Meaning Example Matches
. any one character c.t cat, cut, c7t
^ and $ start and end of the text ^cat$ exactly cat
[abc] one character from the set gr[ae]y gray, grey
[a-z0-9] one character from a range [A-Z][a-z]+ Lyon
[^abc] one character not in the set [^0-9] any non-digit
* zero or more of the previous ab* a, abbb
+ one or more [0-9]+ 7, 2024
? zero or one (optional) colou?r color, colour
, {n,m} exactly n, or n to m [0-9]{3} 555
| either side cat|dog cat, dog
( ) group (and capture) (ab)+ abab
\. a literal special character \.com .com

Three details cause most bugs:

  • Unanchored patterns match anywhere. [0-9]{5} also matches inside A123456. Add ^ and $ to demand that the whole value fits.
  • . is any character, including the dot you meant. Write \. for a real dot, and [.] works too.
  • Shortcuts like \d, \w, \s are not universal. PostgreSQL, Oracle and modern MySQL accept them, but plain POSIX engines and many older versions don't. [0-9] and [[:digit:]] work everywhere, so this lesson prefers [0-9].

The functions, by database

Each database wraps the pattern language in a slightly different set of functions. The names REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_COUNT come from Oracle and were copied elsewhere:

Job Oracle MySQL 8 / MariaDB PostgreSQL SQLite
Does it match? REGEXP_LIKE(s, p) s REGEXP p, REGEXP_LIKE(s, p) s ~ p (case-insensitive: ~*), REGEXP_LIKE (15+) s REGEXP p once you supply the function
Doesn't match NOT REGEXP_LIKE(s, p) s NOT REGEXP p s !~ p, s !~* p s NOT REGEXP p
Replace REGEXP_REPLACE(s, p, r) REGEXP_REPLACE(s, p, r) REGEXP_REPLACE(s, p, r, 'g') your own function
First match REGEXP_SUBSTR(s, p) REGEXP_SUBSTR(s, p) substring(s from p), REGEXP_MATCH(s, p), REGEXP_SUBSTR (15+) your own function
Position of match REGEXP_INSTR(s, p) REGEXP_INSTR(s, p) REGEXP_INSTR (15+) your own function
Count matches REGEXP_COUNT(s, p) none built in REGEXP_COUNT (15+) your own function
Split into rows none built in none built in REGEXP_SPLIT_TO_TABLE(s, p) none

SQL Server added a matching family (REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, and set-returning variants) in SQL Server 2025 at compatibility level 170. Older versions have only LIKE with [ ] sets and PATINDEX.

Some behaviors differ in ways that matter. REGEXP_REPLACE replaces every match in Oracle and MySQL, but only the first one in PostgreSQL unless you pass the 'g' flag. Case-insensitive matching is a flag: 'i' in Oracle, MySQL and PostgreSQL's function forms, or the ~* operator. A match against NULL returns NULL, so rows with missing values drop out of both WHERE s ~ p and WHERE s !~ p.

SQL
-- PostgreSQL
SELECT email
FROM customers
WHERE email ~* '^[a-z0-9._%+-]+@[a-z0-9-]+(\.[a-z0-9-]+)+$';

SELECT regexp_replace(phone, '[^0-9]', '', 'g') AS digits FROM customers;
SELECT substring(email from '@(.+)$') AS domain FROM customers;   -- the group, without the @

-- MySQL 8 (backslashes must be doubled inside string literals)
SELECT email FROM customers WHERE REGEXP_LIKE(email, '^[a-z0-9._%+-]+@[a-z0-9-]+(\\.[a-z0-9-]+)+$', 'i');
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS digits FROM customers;
SELECT REGEXP_SUBSTR(email, '[^@]+$') AS domain FROM customers;

-- Oracle: the sixth argument of REGEXP_SUBSTR picks a capture group
SELECT REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1) AS domain FROM customers;
SELECT REGEXP_COUNT(notes, '[A-Za-z]+') AS words FROM customers;

Filtering and validating

The first live example creates a small contact table with deliberately messy data, registers a regexp function, and uses the operator to find well-formed emails and phone numbers. SQLite rewrites value REGEXP pattern into a call regexp(pattern, value), which is why the pattern comes first in the Python function:

Python Validate emails and phone numbers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Three of the five emails pass; not-an-email has no @ and alan@@example.com has two. Four phone numbers fit the US pattern (the brackets, dashes and dots are all optional separators), while the +44 number is rejected. Notice the anchors ^ and $: without them not-an-email would still fail, but a value like see [email protected] please would pass.

No regex can fully validate an email address. The pattern above accepts what real-world sign-up forms usually want, but valid addresses can be stranger (quoted local parts, internationalized domains), and a perfectly formed address can still not exist. Use a regex to catch typos, and a confirmation email to prove ownership.

Extracting and replacing

REGEXP_SUBSTR returns the part of a string that matched, and REGEXP_REPLACE rewrites the matches. Together they clean messy columns. The stand-ins below follow the Oracle shape (value, pattern, group number) and add a counter like REGEXP_COUNT:

Python Extract, replace and count

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The domain column holds example.com, navy.example.org and kernel.example. The digits column keeps only digits, and normalized rewrites all three phone formats to 555-010-4477 style using back-references: \1, \2 and \3 stand for what the first, second and third ( ) group captured (PostgreSQL and Oracle use the same \1 syntax; MySQL 8 uses $1). The word count for wrote the first program is 4.

Experimenting with the syntax

To get a feel for the pieces, run a handful of patterns against the same list of words and see which ones match. Anchors and quantifiers change the result more than you'd expect:

Python Which words match which pattern?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare the first two lines: ^c.t$ matches cat, cut and c4t, while ^ca?t$ matches only cat (and would match ct). The unanchored c[a-z]*t finds cat, cart, coat, cut and also catalog, because the pattern may start and stop anywhere inside the word.

When you have no regex: GLOB and LIKE

Plain SQLite and old SQL Server editions have no regex, but a character-class check is often enough. SQLite's GLOB supports [0-9] and [^0-9] sets:

SQL A ZIP code check without regex

Press Run (or Ctrl + Enter) to see the result here.

12345 and 00501 pass both checks, while 1234 (too short), 12a45 (a letter) and 123456 (too long) fail. The second form says "length 5 and no character outside 0-9", which scales to any length without repeating the class. SQL Server's LIKE '[0-9][0-9][0-9][0-9][0-9]' and NOT LIKE '%[^0-9]%' are the same idea.

Pitfalls and good habits

  • Escaping differs. MySQL treats backslash as an escape inside string literals, so a regex dot is written '\\.'. PostgreSQL's default settings pass '\.' straight to the regex engine; Oracle and SQL Server do too. Keep patterns in parameters or constants so you only get this right once.
  • Regex rarely uses indexes. WHERE email ~ '...' normally scans the table. PostgreSQL's pg_trgm extension can index some patterns, and a leading literal prefix may use a plain index in some setups, but never count on it. Narrow the rows with an indexed condition first and apply the regex to what is left.
  • Catastrophic backtracking. A pattern like (a+)+$ on a long string of as can take exponential time in backtracking engines. Don't accept user-written patterns, keep your own simple, and set a statement timeout.
  • Prefer constraints for validation. CHECK (email ~ '...') (PostgreSQL) or CHECK (REGEXP_LIKE(...)) (MySQL 8.0.16+, Oracle) stops bad data at the door instead of finding it later.
  • Know the flavor. MySQL 8 uses the ICU engine, PostgreSQL its own "advanced" POSIX engine, Oracle a POSIX-based one, and MariaDB PCRE. Features like lookbehind ((?<=@)), lazy quantifiers (*?) and \b are not available everywhere, so test on your target database.

For tasks the other lessons already cover, don't reach for regex: use LIKE and GLOB for simple wildcards, the string functions for cutting and replacing known text, and data cleaning techniques for the wider picture.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which pattern accepts only values made of exactly five digits?

    The anchors ^ and $ force the whole value to match. Without them, [0-9]{5} also matches five digits inside a longer string such as A123456, and ^[0-9]+$ accepts any number of digits.
  2. 2. What is the difference between REGEXP_REPLACE in Oracle/MySQL and in PostgreSQL by default?

    In PostgreSQL, regexp_replace(s, p, r) replaces only the first match; add the 'g' flag for all matches. Oracle and MySQL replace all matches by default.
  3. 3. In SQLite, what does value REGEXP pattern do?

    SQLite only provides the syntax. The REGEXP operator calls a function named regexp with the pattern first and the value second, and the program (or an extension) has to register it.
  4. 4. A row has email = NULL. What does WHERE email !~ '^[a-z]+@' return for it in PostgreSQL?

    Matching against NULL yields NULL, and NOT NULL is still NULL. WHERE only keeps rows where the condition is true, so you must add OR email IS NULL if you want those rows.
  5. 5. Why is a regex-based email check not a complete validation?

    A pattern checks shape only. It can catch typos, but only a confirmation email proves that the mailbox exists and belongs to the user.

Summary

  • A regular expression describes text with anchors (^, $), sets ([a-z]), quantifiers (*, +, ?, {n,m}), alternation (|) and groups (( )); anchor the pattern when the whole value must match.
  • Oracle and MySQL offer REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR (plus REGEXP_COUNT in Oracle); PostgreSQL uses ~, ~*, regexp_replace(..., 'g'), substring(s from p) and regexp_match; SQL Server 2025 adds its own family.
  • SQLite supplies only the REGEXP keyword: register a regexp(pattern, value) function, or fall back to GLOB character classes.
  • Use regex to filter, validate, extract and clean, but remember escaping rules, NULL results, missing index support and backtracking cost.
  • Prefer LIKE, string functions or a CHECK constraint when they are enough.

Next: JSON Functions, where you'll query and build JSON values.