# `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

```sql
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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/set-returning-functions.md#with-ordinality).
- Returns captured groups as a `text[]` array across a set of rows. For a single scalar result instead, use [REGEXP_SUBSTR](/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-substr.md), which returns the text of the first captured group of the first match directly.
- For detailed regex syntax, see [Regular Expression Syntax](/docs/data/data-cloud-query-guide/references/dc-sql-reference/string-matching.md#regular-expression-syntax).

## Examples

### Basic Capture

Extract two captured groups from the first match.

```sql
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.

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

Returns:

| groups            |
| :---------------- |
| ["bar", "beque"]  |
| ["bazil", "barf"] |

### Numbering Matches with WITH ORDINALITY

```sql
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

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

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

## Related Documentation

- [REGEXP_SUBSTR](/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-substr.md)
- [Set Returning Functions](/docs/data/data-cloud-query-guide/references/dc-sql-reference/set-returning-functions.md)
- [Regular Expression Syntax](/docs/data/data-cloud-query-guide/references/dc-sql-reference/string-matching.md#regular-expression-syntax)