REGEXP_EXTRACT_ALL_ARRAY

SinceVersion dev

Description

Extracts every non-overlapping match of the first capturing group in <pattern> from <str>, returning a string array in match order. Returns NULL if either argument is NULL.

Syntax

REGEXP_EXTRACT_ALL_ARRAY(<str>, <pattern>)

Parameters

Parameter	Description
<str>	Input string, of type STRING.
<pattern>	Regular expression, of type STRING. The first capturing group is extracted; a third group-index argument is not supported.

Return Value

Returns ARRAY<STRING>. Returns an empty array [] if there is no match or no capturing group. Returns NULL if either argument is NULL. An invalid regular expression raises an error.

The default engine uses RE2 syntax and case-sensitive matching. The session variable enable_extended_regex enables advanced regular expression features; see REGEXP_EXTRACT_ALL. This function returns an array, while REGEXP_EXTRACT_ALL returns a formatted string.

Example

SELECT regexp_extract_all_array('id=12,id=34', 'id=([0-9]+)') AS result;

["12", "34"]

Use (?i) for case-insensitive matching:

SELECT regexp_extract_all_array('Hello hello HELLO', '(?i)(hello)') AS result;

["Hello", "hello", "HELLO"]

SELECT regexp_extract_all_array('abc', '([0-9]+)') AS no_match,
       regexp_extract_all_array('abc', 'abc') AS no_capture_group;

[]    []

SELECT regexp_extract_all_array(NULL, '(a)') AS null_input,
       regexp_extract_all_array('a', NULL) AS null_pattern;

NULL    NULL

The result can be passed directly to array functions:

SELECT array_size(regexp_extract_all_array('a1b2', '([0-9])')) AS matches;

2