REGEXP_MATCHES

Returns an array of all substrings that match a regular expression pattern. If the pattern does not match, returns an empty array.

REGEXP_MATCHES(<string>, <pattern>[,'<flag>[...]'])

Parameter	Description
`<string>`	The string from which to extract substrings, based on a regular expression
`<pattern>`	An re2 regular expression for matching with the string.
`<flag>`	Optional. Flags allow additional controls over characters used in the regular expression matching. If using multiple flags, you can include them in the same single-quote block without any separator character. Firebolt supports the following re2 flags to override default matching behavior.* `i` - Specifies case-insensitive matching. * `m` - Specifies multi-line mode. In this mode, `^` and `$` characters in the regex match the beginning and end of line. * `s` - Specifies that the `.` metacharacter in regex matches the newline character in addition to any character in `.` * Specifies Ungreedy mode. In this mode, the meaning of the metacharacters `` and `+` in regex `<pattern>` are swapped with `?` and `+?`, respectively. See the examples using flags below for the difference in how results are returned.

Example

SELECT
	REGEXP_MATCHES('ABC', '^([A-Z]+)');

Returns: ["ABC"]

SELECT
	REGEXP_MATCHES('Learning #Firebolt #REGEX', '#([A-Za-z0-9_]+)');

Returns: ["Firebolt", "REGEX"]

Example–using flags

The i flag causes the regular expression to be case insensitive. Without this flag, this query would only match and return ABC.

SELECT
	REGEXP_MATCHES('ABCdef', '^([A-Z]+)', 'i');

Returns: ["ABCdef"]

The U flag causes metacharacters like + to return as few characters together as possible. Without this flag, this query would return ["PPL","P"].

SELECT
	REGEXP_MATCHES('aPPLePie', '([A-Z]+)', 'U');

Returns: ["P","P","L","P"]