---
title: "Regular expressions in SQL"
description: "SQL engines support regular expressions through functions and operators like REGEXP_MATCHES, REGEXP_REPLACE, and SIMILAR TO for pattern-based text matching and extraction."
canonical: "https://motherduck.com/glossary/regular-expressions-sql/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "Analyze JSON Data Using SQL and DuckDB"
    url: "https://motherduck.com/blog/analyze-json-data-using-sql/"
  - title: "Expressions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/expressions/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# Regular expressions in SQL

> SQL engines support regular expressions through functions and operators like REGEXP_MATCHES, REGEXP_REPLACE, and SIMILAR TO for pattern-based text matching and extraction.

## 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.

<glossary-callout guide="duckdb-book-brief" />

## Common patterns
```sql
-- 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:

```sql
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.