REGEXP_EXTRACT_ALL

​

Syntax

​

Scalar

regexp_extract_all(value, pattern [, group])

​

Arguments

​

Returns

• Type: ARRAY<STRING>
• Possible values: Matching capture text; an empty array when nothing is captured.
• NULL behavior: NULL value or pattern returns NULL. A NULL group index returns NULL with a constant pattern, but is not reliably propagated with a pattern column.
• Shape: One string for each participating selected capture.
• Ordering: Match encounter order.

​

Properties

​

Examples

​

Typical behavior

SELECT regexp_extract_all('a1b2', '([0-9])', 1) AS "value", regexp_extract_all('abc', '([0-9])', 1) AS no_match;

​

NULL scalar group

SELECT regexp_extract_all('a1b2','([0-9])',CAST(NULL AS INT)) AS "value";

​

Default capture group and full match

SELECT
  regexp_extract_all('a1b2', '([a-z])([0-9])') AS default_group,
  regexp_extract_all('a1b2', '([a-z])([0-9])', 0) AS full_match;

​

Limitations

• Use a constant capture-group index. A group column is not evaluated independently for each row.
• Invalid regular expressions raise an error.
• Negative or out-of-range group indexes return an empty array.
• A NULL group index is not reliably propagated when pattern is a column; use a non-NULL constant index.