REGEXP_SUBSTR
Returns the substring that matches a regular expression.
PostgreSQL 17.5
REGEXP_SUBSTR(string, pattern[, start[, occurrence[, flags[, subexpr]]]])Parameters
- stringtext
- String to search in
- patterntext
- Regular expression
- startintegeroptional
- Position to start the search from, 1 by default
- occurrenceintegeroptional
- Number of the match, 1 by default
- flagstextoptional
- Flags, for example i to ignore case
- subexprintegeroptional
- Number of the group in parentheses to return; 0 by default, the whole match
Return value
A string (text); NULL if there is no match
Examples
First and second number
PostgreSQL 17.5
SELECT REGEXP_SUBSTR('Order 42 from 2024-03-08', '[0-9]+') AS first_number,
REGEXP_SUBSTR('Order 42 from 2024-03-08', '[0-9]+', 1, 2) AS second_number;Domain from email using a group
PostgreSQL 17.5
SELECT email,
REGEXP_SUBSTR(email, '@(.+)$', 1, 1, '', 1) AS domain
FROM Users
LIMIT 5;Details
The function appeared in PostgreSQL 15. Unlike REGEXP_MATCH, it returns a string, not an array. To set subexpr, pass all the arguments before it: flags can be an empty string ''.