REGEXP_MATCHES
Returns matches of a regular expression: with the g flag, all of them, one result row per match.
PostgreSQL 17.5
REGEXP_MATCHES(string, pattern[, flags])Parameters
- stringtext
- String to search in
- patterntext
- Regular expression
- flagstextoptional
- Flags: g for all matches, i to ignore case
Return value
A set of text[] arrays; an empty set if there are no matches
Examples
All numbers in a string
PostgreSQL 17.5
SELECT REGEXP_MATCHES('a1b22c333', '[0-9]+', 'g') AS numbers;Parts of each date
PostgreSQL 17.5
SELECT REGEXP_MATCHES(
'2024-03-08, 2025-12-31',
'([0-9]{4})-([0-9]{2})-([0-9]{2})',
'g'
) AS date_parts;Capital letters in names
PostgreSQL 17.5
SELECT member_name,
(REGEXP_MATCHES(member_name, '[A-Z]', 'g')) [1] AS capital
FROM FamilyMembers;Details
Each match is a text[] array: one element per group in parentheses, or a single element with the whole match. Without the g flag only the first match is returned.
The function returns a set of rows, so in SELECT it multiplies a table row, and if there are no matches, the row disappears from the result. To keep the row, use REGEXP_MATCH: it returns one match or NULL.