regexp_matches

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the captured groups of each match of a regular expression pattern against a string, as a set of rows.

1regexp_matches(<string>, <pattern> [, flags])

• <string>: The string to search.
• <pattern>: The regular expression pattern to match. Must contain at least one capturing group.

• flags: A string of option flags that modify matching behavior. Use g to return every match instead of only the first.

Returns a set of rows, each containing a text[] array of the captured groups for one match.

• Without the g flag, returns at most one row (the first match).
• With the g flag, returns one row per match.
• Returns no rows if <string> or <pattern> is NULL.

• The pattern must contain at least one capturing group. A pattern with no capturing groups raises an error rather than returning the whole match. This differs from PostgreSQL’s regexp_matches, which falls back to the whole match when there are no groups.
• Uses RE2/POSIX regular expression syntax.
• Supports WITH ORDINALITY to number the returned rows. See Set Returning Functions.
• Returns captured groups as a text[] array across a set of rows. For a single scalar result instead, use REGEXP_SUBSTR, which returns the text of the first captured group of the first match directly.
• For detailed regex syntax, see Regular Expression Syntax.

Extract two captured groups from the first match.

1SELECT * FROM regexp_matches('foobarbequebaz', '(bar)(beque)') AS m(groups)

Returns one row: ["bar", "beque"].

Use the g flag to return every match, one row per match.

1SELECT * FROM regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g') AS m(groups)

Returns:

1SELECT * FROM regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g') WITH ORDINALITY AS m(groups, ordinality)

Returns:

1SELECT * FROM regexp_matches('S', 'S') AS m(groups)

Raises an error because the pattern doesn’t contain a capturing group.

• REGEXP_SUBSTR
• Set Returning Functions
• Regular Expression Syntax