Regular expressions in SQL

Overview

While LIKE handles simple wildcard matching, regular expressions (regex) let SQL queries match, extract, and rewrite text using far more expressive patterns — character classes, quantifiers, alternation, and capture groups. Most SQL engines expose regex through a family of functions rather than a single operator.

Common patterns

Copy code
-- Does the column match a pattern?
SELECT * FROM users WHERE REGEXP_MATCHES(email, '^[\w.+-]+@[\w-]+\.[a-z]{2,}$');

-- Extract the first capture group
SELECT REGEXP_EXTRACT(url, 'https?://([^/]+)/', 1) AS host FROM page_views;

-- Replace matches
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits_only FROM contacts;

The standard SQL SIMILAR TO operator offers a lighter-weight, POSIX-flavored alternative to full regex functions, but is less commonly used than dedicated REGEXP_* functions in modern engines.

DuckDB specifics

DuckDB provides a rich, PCRE-based (via RE2) regex toolkit: regexp_matches(string, pattern) for boolean matching, regexp_replace(string, pattern, replacement, options) for substitution (with a 'g' option flag for global replace), regexp_extract(string, pattern, group) for pulling out one capture group, and regexp_extract_all(string, pattern, group) — a DuckDB convenience that returns every match as a LIST rather than just the first:

Copy code
SELECT regexp_extract_all('order-12, order-45, order-99', '\d+') AS order_ids;
-- ['12', '45', '99']

DuckDB also supports the Postgres-style ~ (matches) and !~ (does not match) operators as shorthand for regexp_matches, and SIMILAR TO for lighter pattern matching. Because DuckDB's regex engine is RE2-based, it does not support backreferences in patterns, unlike PCRE-based engines.