In PostgreSQL, the regexp_match() function returns a text array containing substrings that match a given POSIX regular expression in a given string. Only the first match is returned (to return all matches, use regexp_matches() instead).

We pass the string as the first argument and the pattern as the second argument. We can also provide a flag as an optional third argument, which determines how the function behaves.

Example

Here’s a basic example to demonstrate:

SELECT regexp_match('Thailand', 'land');

Result:

{land}

In this case there was a match and so the substring was returned. As mentioned, the result is returned in a text array.

Only the first match is returned:

SELECT regexp_match('Thailand Iceland', 'land');

Result:

{land}

No Match

When there’s no match, null is returned:

SELECT regexp_match('Thailand', 'Siam');

Result:

null

Multiple Matching Substrings

When there are multiple matching substrings, each substring is returned as a separate array element:

SELECT regexp_match('Thailand', '(Tha)(ila)(nd)');

Result:

{Tha,ila,nd}

More Examples

Here are some more examples:

\x
SELECT 
    regexp_match('This, that, and the other', '(that)(.)') AS "(that)(.)",
    regexp_match('This, that, and the other', '(that)((.)*)(other)') AS "(that)((.)*)(other)",
    regexp_match('This, that, and the other', '\A(that)') AS "\A(that)",
    regexp_match('This, that, and the other', '\A(This)') AS "\A(This)",
    regexp_match('This, that, and the other', '\A(This)((.)*)(other)') AS "\A(This)((.)*)(other)";

Result (using vertical output):

(that)(.)             | {that,","}
(that)((.)*)(other)   | {that,", and the "," ",other}
\A(that)              | null
\A(This)              | {This}
\A(This)((.)*)(other) | {This,", that, and the "," ",other}

Add a Flag

We can also add a flag to change the behaviour of the function. For example, we can specify that the search is case-insensitive by including the i flag:

SELECT 
    regexp_match('Thailand', 'Land') AS "Case Sensitive",
    regexp_match('Thailand', 'Land', 'i') AS "Case Insensitive";

Result:

 Case Sensitive | Case Insensitive 
----------------+------------------
 null           | {land}

The i flag is just one of many flags we can use. The following table contains the flags we can use here:

Option	Description
b	Rest of RE is a BRE
c	Case-sensitive matching (overrides operator type)
e	Rest of RE is an ERE
i	Case-insensitive matching (overrides operator type)
m	Historical synonym for n
n	Newline-sensitive matching
p	Partial newline-sensitive matching
q	Rest of RE is a literal (“quoted”) string, all ordinary characters
s	Non-newline-sensitive matching (default)
t	Tight syntax (default)
w	Inverse partial newline-sensitive matching
x	Expanded syntax

Source: https://www.postgresql.org/docs/current/functions-matching.html#POSIX-METASYNTAX

More About POSIX Regular Expressions in PostgreSQL

See the PostgreSQL documentation for an overview of how POSIX regular expressions work and the various options available when using the regex_match() function.