REGEXP_MATCH
MySQL equivalent:
Returns an array of substrings from the first match of a regular expression.
PostgreSQL 17.9
REGEXP_MATCH(string, pattern[, flags])- [ ]optional part
- string
- String to search intext
- pattern
- Regular expressiontext
- flags
- Flags, for example i to ignore casetextOptionalbceimnpqstwx
Return value
A text array (text[]) from the first match; NULL if there is no match
Examples
Date parts from text
PostgreSQL 17.9
SELECT REGEXP_MATCH(
'Order from 2024-03-08',
'([0-9]{4})-([0-9]{2})-([0-9]{2})'
);Username and domain from email
PostgreSQL 17.9
SELECT email,
REGEXP_MATCH(email, '^(.+)@(.+)$') AS parts
FROM Users
LIMIT 5;Domain as an array element
PostgreSQL 17.9
SELECT email,
(REGEXP_MATCH(email, '@(.+)$')) [1] AS domain
FROM Users
LIMIT 5;Details
If the pattern contains groups in parentheses, the array holds one element per group, otherwise a single element with the whole match. A single element is taken by its number: (REGEXP_MATCH(...))[1]. The g flag is not supported: all matches are returned by REGEXP_MATCHES(..., 'g'), one result row per match. To get the match as a string rather than an array, use REGEXP_SUBSTR, available in PostgreSQL since version 15.