regexp_matches()

A function returning matches for a regular expression

regexp_matches() is a system function returning substrings which match a POSIX regular expression.

regexp_matches() was added in PostgreSQL 8.3.

Usage

regexp_matches ( string text, pattern text [, flags text ] ) → setof text[]

Normally it makes sense to provide at least one flag, typically 'g', otherwise regexp_matches() behaves in exactly the same way as regexp_match() (available from PostgreSQL 10). A list of flags other than 'g' is available in the PostgreSQL documentation: ARE Embedded-Option Letters.

Change history

Examples

Basic usage example for regexp_matches():

postgres=# SELECT regexp_matches('foobarboo', '.oo', 'g');
 regexp_matches 
----------------
 {foo}
 {boo}
(2 rows)

Retrieving multiple matches:

postgres=# SELECT regexp_matches('foobarboo floobiloo', '(.oo).+?(.oo)', 'g');
 regexp_matches 
----------------
 {foo,boo}
 {loo,loo}
(2 rows)

Note that using regexp_matches() without the 'g' flag is equivalent to regexp_match():

postgres=# SELECT regexp_match('foobarboo', '.oo'), regexp_matches('foobarboo', '.oo');
 regexp_match | regexp_matches 
--------------+----------------
 {foo}        | {foo}
(1 row)

Categories

String manipulation, System function

See also

regexp_match()