REGEXP_MATCHES

Returns matches of a regular expression: with the g flag, all of them, one result row per match.

PostgreSQL 17.9

REGEXP_MATCHES(string, pattern[, flags])

[ ]optional part

string: String to search in
text
pattern: Regular expression
text
flags: Flags, for example 'gi'
textOptional
bcegimnpqstwx

Return value

A set of text[] arrays; an empty set if there are no matches

Examples

All numbers in a string

PostgreSQL 17.9

SELECT REGEXP_MATCHES('a1b22c333', '[0-9]+', 'g') AS numbers;

numbers
["1"]
["22"]
["333"]

Parts of each date

PostgreSQL 17.9

SELECT REGEXP_MATCHES(
		'2024-03-08, 2025-12-31',
		'([0-9]{4})-([0-9]{2})-([0-9]{2})',
		'g'
	) AS date_parts;

date_parts
["2024","03","08"]
["2025","12","31"]

Capital letters in names

PostgreSQL 17.9

SELECT member_name,
	(REGEXP_MATCHES(member_name, '[A-Z]', 'g')) [1] AS capital
FROM FamilyMembers;

member_name	capital
Headley Quincey	H
Headley Quincey	Q
Flavia Quincey	F
Flavia Quincey	Q
Andie Quincey	A
Andie Quincey	Q
Lela Quincey	L
Lela Quincey	Q
Annie Quincey	A
Annie Quincey	Q

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.

REGEXP_MATCHES

Return value

Examples

All numbers in a string

Parts of each date

Capital letters in names

Details

See also