regexp_substr()

A function returning a substring matching the regular expression

regexp_substr() is a system function returning a substring matching the specified POSIX regular expression.

regexp_substr() was added in PostgreSQL 15.

Usage

regexp_substr ( string text, pattern text [, start integer [, N integer [, flags text [, subexpr integer ] ] ] ] ) → text

A list of flags which can be used with regexp_substr() is available in the PostgreSQL documentation: ARE Embedded-Option Letters.

Basic usage example for regexp_substr(), matching the first occurrence of the specified regular expression:

postgres=# SELECT regexp_substr('foobarboo flooobilooo', '\wlo{1,}');
 regexp_substr
---------------
 flooo
(1 row)

Matching the second occurrence of the specified regular expression:

postgres=# SELECT regexp_substr('foobarboo flooobilooo', '\wlo{1,}', 1, 2);
 regexp_substr
---------------
 ilooo
(1 row)