---
schema_version: "1"
id: f8d16947507457525e5888a8e761c05f72d383e0af57535fe79d8101232b8fdb
title: regexp_matches
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/set-returning-func/regexp-matches.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-matches.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 1144199a6f33c66ed2565f55c557128826d4d9aa67f1492646b3a90a77bef768
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# `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](https://developer.salesforce.com/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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/scalar-func/string-matching/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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/scalar-func/string-matching/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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/scalar-func/string-matching/regexp-substr.md)
- [Set Returning Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/set-returning-functions.md)
- [Regular Expression Syntax](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/scalar-func/string-matching/string-matching.md#regular-expression-syntax)