Salesforce Docs Portal

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.

Syntax

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

Arguments

Required

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

Optional

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

Flags

Flag Description
g Global matching — return every match, not just the first

Returns

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.

Considerations

  • 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.

Examples

Basic Capture

Extract two captured groups from the first match.

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

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

Global Matching

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

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

Returns:

groups
["bar", "beque"]
["bazil", "barf"]

Numbering Matches with WITH ORDINALITY

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

Returns:

groups ordinality
["bar", "beque"] 1
["bazil", "barf"] 2

Pattern Without a Capturing Group

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

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

Related Documentation