REGEXP_MATCHES in PostgreSQL: Capture Groups and the g Flag

REGEXP_MATCHES in PostgreSQL extracts regular-expression matches from a string and returns the captured groups as a text[] array. It is not a scalar function: without flags it yields one result row per call, and with the g flag it yields one row per match. Grasping this set-returning nature kills half of the mysterious bugs you will hit.

The basic case: groups in an array

The function returns an array of captured groups. If the pattern has no groups, the whole match lands in the array. To pull a value out, index the array ([1] is the first group):

SELECT (REGEXP_MATCHES(email, '^([^@]+)@(.+)$'))[1] AS local_part,
       (REGEXP_MATCHES(email, '^([^@]+)@(.+)$'))[2] AS domain
FROM users;

A classic task is pulling a numeric id out of text. Parentheses define the group, \d+ matches a run of digits:

SELECT id,
       (REGEXP_MATCHES(name, 'order-(\d+)'))[1] AS order_no
FROM orders
WHERE name ~ 'order-\d+';

The big gotcha: no match means no row

REGEXP_MATCHES behaves like an INNER JOIN, not a scalar expression. When nothing matches, the function returns zero rows, and the table row silently drops out of the result. That is not a NULL — the record simply vanishes.

-- DANGER: users with no digits in their name just disappear
SELECT id, (REGEXP_MATCHES(name, '(\d+)'))[1] AS digits
FROM users;

• If you need every table row, do not call the function directly in SELECT.
• The safe alternative is regexp_substr (PostgreSQL 15+), which returns NULL:

SELECT id, regexp_substr(name, '\d+') AS digits
FROM users;

Or wrap it in a LEFT JOIN LATERAL to preserve unmatched rows.

The g flag: one row per match

The third argument holds flags. The g (global) flag turns the function into a generator: one input string produces as many rows as there are matches. This shines for parsing lists and tokens:

SELECT id,
       (REGEXP_MATCHES(status, '(\w+)', 'g'))[1] AS token
FROM orders;

To collect tokens back into one array per order, combine it with an aggregate or LATERAL:

SELECT o.id,
       array_agg(m.token) AS tokens
FROM orders o
CROSS JOIN LATERAL (
  SELECT (REGEXP_MATCHES(o.status, '([a-z]+)', 'g'))[1] AS token
) AS m
GROUP BY o.id;

Other handy flags: i for case-insensitive matching, n so the dot does not cross newlines.

REGEXP_MATCHES vs regexp_substr

Pick the tool that fits the job:

• REGEXP_MATCHES when you need all groups or all matches (the g flag). It returns an array and filters out non-matching rows.
• regexp_substr when you need one substring and must keep the row: it returns NULL instead of dropping the record.

-- Extract the domain for every user without losing rows
SELECT id,
       email,
       regexp_substr(email, '@(.+)$', 1, 1, '', 1) AS domain
FROM users;

MySQL 8 has no REGEXP_MATCHES; the nearest analog is REGEXP_SUBSTR(col, pattern) (no group capture before 8.0.x) or REGEXP_REPLACE for extraction. In ClickHouse, use extractAll(s, pattern) (the g-flag equivalent) and extract(s, pattern) for a single group. Remember: the "no match, no row" semantics are unique to REGEXP_MATCHES, and they are what most often breaks reports.