Lesson 7.5

Regular Expressions in SQL

A regular expression describes the shape of text: digits, then letters. regexp_matches finds rows that fit, and regexp_extract pulls out the part you need.

LIKE knows two wildcards: any one character, and any run of characters. It can't say "one or more digits" or "a number followed by letters". A regular expression can. It's a small language for describing the shape of text, and DuckDB has three functions that use one:

regexp_matches(text, pattern) is true when the pattern appears anywhere in the text.
regexp_extract(text, pattern, group) returns the part of the text the pattern found.
regexp_replace(text, pattern, replacement, 'g') swaps what it found for something else.

\d means one digit, so this finds the six products with a number in their name:

Pattern

select product_name
from products
where regexp_matches(product_name, '\d')
order by product_id

The pieces of a pattern

Most patterns are built from a handful of pieces. Every other character matches itself.

Piece	Matches
`\d`	One digit, 0 to 9
`[a-z]`, `[a-zA-Z]`	One character from the range
`[^0-9]`	One character that is not in the range
`.`	Any one character
`+`, `*`, `?`	The piece before it, one or more times, any number of times, or optionally
`{3}`	The piece before it, exactly three times
`^`, `$`	The start and the end of the text
`(pot\|plant)`	Either alternative, and a group you can extract
`\.`	A real dot. A backslash makes any special character literal

So \d+[a-zA-Z]+ reads as "one or more digits, then one or more letters", and matches 20L and 25ft.

Pulling a value out

Wrap the part you want in parentheses to make it a group, then pass its number to regexp_extract. Group 0, the default, is the whole match. This takes the name between the @ and the first dot of each supplier's email:

Group 1 is the part in parentheses

select supplier_name,
       contact_email,
       regexp_extract(contact_email, '@([a-z]+)\.', 1) as mail_domain
from suppliers
order by supplier_id

Two suppliers have no email. A NULL in gives NULL out. Text that doesn't match is different: DuckDB returns an empty string, '', not NULL.

Replacing what matches

regexp_replace changes only the first match unless you pass 'g' (global) as the fourth argument. Here [^0-9] matches every character that isn't a digit, so the phone numbers keep only their digits:

Strip everything but digits

select phone,
       regexp_replace(phone, '[^0-9]', '', 'g') as digits
from customers
where phone is not null
order by customer_id
limit 3

Leave out the 'g' and 215-555-0142 becomes 215555-0142. See why regexp_replace changes only the first match.

Ignoring case

Patterns are case-sensitive. Pass 'i' as the last argument to ignore case. Every product name is capitalized, so without it, (pot|plant) finds nothing. With it, the pattern finds six, from Snake Plant to Potting Soil 20L:

The 'i' option

select product_name
from products
where regexp_matches(product_name, '(pot|plant)', 'i')
order by product_id

When an extract feeds a cast

Because a missed match is '', casting the result of every row fails on the first product without a number:

Casting an empty match

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

DuckDB stops with Could not convert string '' to INT32. Keep only the rows that match first, with regexp_matches in the WHERE clause, or use try_cast, which turns '' into NULL.

Dialect note

Every engine spells these differently. PostgreSQL uses ~ for "matches anywhere" and substring(text from pattern) to extract. In DuckDB, ~ means the whole text must match, so 'Potting Soil 20L' ~ '\d+' is false. MySQL and Oracle have REGEXP_LIKE and REGEXP_SUBSTR. The regular expressions reference lists each engine's names.

Syntax reference

Need syntax details? See the SQL regular expressions reference

Schema · Garden Shop Table · products 9 columns · 24 rows

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Your task

Some products carry a size in their name, such as Potting Soil 20L or Drip Hose 25ft: a number followed straight away by letters. For each product whose name has a size, return product_id, product_name, size_amount (the number, as an integer), and size_unit (the letters). Sort by product_id.