PostgreSQL 18 reference · String Functions
PostgreSQL REGEXP_MATCH() Function
Return the captured groups of the first regular-expression match as a text array, or NULL if there's no match.
PostgreSQL 18 Since 10
Syntax
regexp_match(string, pattern, flags)
Parameters
| Parameter | Description |
|---|---|
string |
The input text. |
pattern |
A POSIX regular expression. |
flags optional |
For example i. |
Return value
A text[] array, or NULL.
Examples
SELECT regexp_match('Order #A-1042', '#([A-Z])-(\d+)') AS parts;
Output
parts
----------
{A,1042}
(1 row)Run this on your computer — see “Get Started” for setup.
SELECT email, (regexp_match(email, '@(.*)$'))[1] AS domain
FROM customers
WHERE email IS NOT NULL
LIMIT 2;
Output
email | domain ------------------+------------- [email protected] | example.com [email protected] | example.com (2 rows)
Run this on your computer — see “Get Started” for setup.
regexp_matches(string, pattern, 'g') returns one row per match instead; regexp_like() (15+) just tests
for a match.
Version history
| Version | Change |
|---|---|
| PostgreSQL 10 | Added |
Related
REGEXP_REPLACE()· Replace text matching a POSIX regular expression. Add the 'g' flag to replace every match, not just the first.REGEXP_COUNT()· Count how many times a regular expression matches in a string. Added in PostgreSQL 15.SUBSTRING()· Extract part of a string by 1-based position and length, or the part matching a regular expression.- SQL String Functions · tutorial