REGEXP_EXTRACT_ALL

Overview

Extracts all substrings from a string that match a specified capture group in a regular expression, returning an ARRAY<STRING>.

Syntax

REGEXP_EXTRACT_ALL(<str>, <pattern>, <group_index>)

Parameters

• <str>: STRING type, the string to search.
• <pattern>: STRING type, a regular expression containing capture groups. Must have at least one () capture group.
• <group_index>: INT type, specifies which capture group to return (1-based index).

Examples

SELECT regexp_extract_all('100-200, 300-400', '([0-9]+)-([0-9]+)', 1); -- ["100","300"] SELECT regexp_extract_all('100-200, 300-400', '([0-9]+)-([0-9]+)', 2); -- ["200","400"] SELECT regexp_extract_all('hello world foo', '([a-z]+)', 1); -- ["hello","world","foo"] SELECT regexp_extract_all('no match here', '([0-9]+)', 1); -- []

Related Documentation

• REGEXP_EXTRACT — extracts the first match
• REGEXP_LIKE — tests whether a string matches a pattern
• REGEXP_REPLACE — replaces matched content