REGEXP_SUBSTR
Returns the substring that matches a regular expression.
PostgreSQL 17.9
REGEXP_SUBSTR(string, pattern[, start[, occurrence[, flags[, subexpr]]]])- [ ]optional part
- string
- String to search intext
- pattern
- Regular expressiontext
- start
- Position to start the search from, 1 by defaultintegerOptional
- occurrence
- Number of the match, 1 by defaultintegerOptional
- flags
- Flags, for example i to ignore casetextOptionalbceimnpqstwx
- subexpr
- Number of the group in parentheses to return; 0 by default, the whole matchintegerOptional
Return value
A string (text); NULL if there is no match
Examples
First and second number
PostgreSQL 17.9
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.9
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 ''.