PostgreSQL regexp_matches() Function

The PostgreSQL regexp_matches() function returns the result of the first match of the specified regular expression from a string; if the g flag used, all matches are returned.

The regexp_matches() function is similar to the regexp_match() function, the difference is regexp_match() only returns the first match, and regexp_matches() returns all matches with the g flag is used,.

The regexp_matches() function uses POSIX regular expressions.

regexp_matches() Syntax

This is the syntax of the PostgreSQL regexp_matches() function:

regexp_matches(string, regex[, flags]) → set of text[]

Parameters

string

Required. The string to search in.

regex

Required. The regular expression.

flags

Optional. The matching mode of the regular expression.

Return value

The PostgreSQL regexp_matches() function returns a set of string arrays, each array in the collection corresponds to each match of the regular expression.

If the string does not contain any match of the regular expression, this function returns null.

If the regular expression contains groups, the match of each group becomes an element of the array.

regexp_matches() Examples

This example shows how to use the regexp_matches() function to get the first match of a regular expression from a string:

SELECT regexp_matches('abcd abcd abcd', 'ab.');

 regexp_matches
----------------
 {abc}

Here, in POSIX regular expression, .means any character.

You can use the g flag in the parameter flags to get all matches of the regular expression. for example:

SELECT regexp_matches('abcd abcd abcd', 'ab.', 'g');

 regexp_matches
----------------
 {abc}
 {abc}
 {abc}

You can use the i flag in the parameter flags to perform a insensitive-case search. for example:

SELECT regexp_matches('Abcd abCd aBcd', 'ab.', 'ig');

 regexp_matches
----------------
 {Abc}
 {abC}
 {aBc}

You can use multiple groups in regular expressions like:

SELECT regexp_matches('Abcd abCd aBcd', '(a.)(c.)', 'ig');

 regexp_matches
----------------
 {Ab,cd}
 {ab,Cd}
 {aB,cd}