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.

  • <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.
FlagDescription
gGlobal matching — return every match, not just 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.

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

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

Returns:

groups
[“bar”, “beque”]
[“bazil”, “barf”]

Returns:

groupsordinality
[“bar”, “beque”]1
[“bazil”, “barf”]2

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