SQLearnAcademy
←Function reference
StringsPostgreSQL only

REGEXP_MATCHES

Extracts the parts of a string that match a regular expression.

Syntax

REGEXP_MATCHES(<text>, '<pattern>')
REGEXP_MATCHES(<text>, '<pattern>', '<flags>')

Returns an array of what it found: if the pattern has bracketed groups, there's one element per group. Without the 'g' flag it finds only the first match; with it — all of them, and each match becomes a separate row of the result. To simply get the first match as text, REGEXP_SUBSTR is easier.

The first match as an array
SELECT REGEXP_MATCHES('Order 1234, box 56', '[0-9]+') AS first_match;
first_match
["1234"]
All numbers as separate rows
SELECT REGEXP_MATCHES('Order 1234, box 56', '[0-9]+', 'g') AS match;
match
["1234"]
["56"]
Login and domain as groupsAdvanced
SELECT (REGEXP_MATCHES('emma@example.com', '(.+)@(.+)'))[1] AS login,
(REGEXP_MATCHES('emma@example.com', '(.+)@(.+)'))[2] AS domain;
logindomain
emmaexample.com

Each pair of brackets in the pattern is a separate group, and [1] and [2] take them from the array.

Watch out

The result is an array, even when a single value was found: {1234}. To get text, take element [1] or use REGEXP_SUBSTR.

If there's no match, REGEXP_MATCHES doesn't give NULL — it gives no rows at all, and the table row disappears from the result. In that case REGEXP_SUBSTR returns NULL and doesn't lose the row.