Functions/Text & patterns/regexp_extract

Text & patterns Scalar

regexp_extract()

If string contains the regexp pattern, returns the capturing group specified by optional parameter group. The group must be a constant value. If no group is given, it defaults to 0. A set of optional options can be set.

Arguments & returns

5 overloads

`regexp_extract(string, pattern[)`

Scalar

Overload link	`string`	`pattern[`	Returns
#01	`VARCHAR`	`VARCHAR`	`VARCHAR`

`regexp_extract(string, pattern[, group = 0][)`

Scalar

Overload link	`string`	`pattern[`	`group = 0][`	Returns
#02	`VARCHAR`	`VARCHAR`	`INTEGER`	`VARCHAR`
#03	`VARCHAR`	`VARCHAR`	`VARCHAR[]`	`VARCHAR`

`regexp_extract(string, pattern[, group = 0][, options])`

Scalar

Overload link	`string`	`pattern[`	`group = 0][`	`options]`	Returns
#04	`VARCHAR`	`VARCHAR`	`INTEGER`	`VARCHAR`	`VARCHAR`
#05	`VARCHAR`	`VARCHAR`	`VARCHAR[]`	`VARCHAR`	`VARCHAR`

Example

SELECT regexp_extract('ABC', '([a-z])(b)', 1, 'i');

Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

More examples

SELECT regexp_extract('abc', '([a-z])(b)', 1);

Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

SELECT regexp_extract('John Doe', '([a-z]+) ([a-z]+)', ['first_name', 'last_name'], 'i');

Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

SELECT regexp_extract('2023-04-15', '(\d+)-(\d+)-(\d+)', ['y', 'm', 'd']);

Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

SELECT regexp_extract('abcde', '[a-z]{3}');

Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

In other engines

Amazon Redshift`regexp_substr()`

SELECT REGEXP_SUBSTR(abc, 'pattern(group)', 2) FROM table

In DuckDB

SELECT REGEXP_EXTRACT(SUBSTRING(abc, 2), 'pattern(group)') FROM "table"

SQLGlot example ↗

Apache Hive`regexp_extract()`

REGEXP_EXTRACT('abc', '(a)(b)(c)')

In DuckDB

REGEXP_EXTRACT('abc', '(a)(b)(c)', 1)

SQLGlot example ↗

BigQuery`regexp_extract()`

Presto`regexp_extract()`

Snowflake`regexp_substr()`

REGEXP_SUBSTR(subject, pattern)

In DuckDB

REGEXP_EXTRACT(subject, pattern)

SQLGlot example ↗

Available in Haybarn 1.5 DuckDB 1.5 DuckDB 1.4 DuckDB 1.3