Link Search Menu Expand Document

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"]