SQL Regular Expressions

A regular expression is a pattern for the shape of text, such as "one or more digits, then letters". DuckDB tests a value against one with regexp_matches, pulls out the matching part with regexp_extract, and rewrites it with regexp_replace. Reach for one when LIKE's two wildcards can't describe what you're looking for.

select product_name,
       regexp_matches(product_name, '\d')                      as has_digit,
       regexp_extract(product_name, '\d+[a-zA-Z]+')            as size,
       regexp_replace(product_name, '\s*\(.*\)', '')          as without_parens
from products
where product_id in (8, 12, 21)
order by product_id

Tulip Bulbs (10pk) has a digit, a size of 10pk, and loses its parentheses. Garden Gnome has no digit, and its size is an empty string.

The functions

Pattern syntax

DuckDB uses the RE2 library, whose syntax is the familiar Perl style without the features that can make a match slow (see below).

In DuckDB a backslash inside a string literal is an ordinary character, so '\d' reaches the regex engine as written. MySQL treats a backslash in a string as an escape, so the same pattern there is '\\d'.

Options

The last argument takes option letters. 'i' ignores case, 'g' makes regexp_replace replace every match, and you can combine them as 'gi'. 's' lets . match a newline, which it otherwise doesn't. An inline (?i) at the start of a pattern also ignores case.

select count(*) filter (where regexp_matches(product_name, 'plant'))      as exact_case,
       count(*) filter (where regexp_matches(product_name, 'plant', 'i')) as any_case
from products

That returns 0 and 3: Snake Plant, ZZ Plant, and Ceramic Planter. An unknown letter is an error: 'x' gives Unrecognized Regex option x.

Extracting parts

Name the groups with a list and regexp_extract returns a struct, one field per group:

select product_name,
       regexp_extract(product_name, '(\d+)([a-zA-Z]+)', ['amount', 'unit']) as size
from products
where regexp_matches(product_name, '\d')
order by product_id

Each field is text: '20' and 'L' for Potting Soil 20L. Read one with size.amount, and cast it before you do arithmetic.

Replacing with a group

In the replacement, \1 stands for what group 1 matched. This turns quoted variety names into parenthesized ones:

select product_name,
       regexp_replace(product_name, '''([^'']+)''', '(\1)') as relabeled
from products
where product_name like '%''%'
order by product_id

Pothos 'Golden' becomes Pothos (Golden).

Validating a format

Anchor the pattern with ^ and $ to check a whole value, not just part of it. 15 of Garden Shop's 20 customers have a phone number in the 215-555-0142 format. The other 5 are NULL, and a NULL phone lands in neither the well-formed nor the malformed count:

select count(*)                                                          as customers,
       count(*) filter (where regexp_matches(phone, '^\d{3}-\d{3}-\d{4}$'))     as well_formed,
       count(*) filter (where not regexp_matches(phone, '^\d{3}-\d{3}-\d{4}$')) as malformed,
       count(*) filter (where phone is null)                                  as missing
from customers

What can go wrong

• No match is '', not NULL. regexp_extract returns an empty string when nothing matches (only a NULL input gives NULL), so coalesce doesn't fill it in and a cast to a number fails. Wrap it in nullif(..., ''), or filter with regexp_matches first.
• regexp_replace changes the first match only. Pass 'g' to change them all. See why regexp_replace changes only the first match.
• ~ means a full match in DuckDB. email ~ 'example' matches none of the 20 customers, while regexp_matches(email, 'example') matches all 20. PostgreSQL code moved to DuckDB silently returns fewer rows. DuckDB has no ~* at all.
• SIMILAR TO takes a regular expression. In DuckDB, email similar to '%example%' matches nothing, because % is a literal character there. In PostgreSQL it's a wildcard, as in LIKE.
• No lookarounds or backreferences in a pattern. RE2 leaves them out so that every match finishes in time proportional to the text. A lookahead fails with invalid perl operator: (?=, and \1 in a pattern with invalid escape sequence: \1. (\1 in a replacement is fine.)

select cast(regexp_extract(product_name, '\d+') as integer) as amount
from products

select regexp_matches(product_name, 'Pot(?=ting)') as potting
from products

The first fails with Could not convert string '' to INT32, and the second with invalid perl operator: (?=.

How other databases differ