A Quick Look at PostgreSQL’s REGEXP_LIKE() Function

In PostgreSQL, we can use the regexp_like() function to check whether or not a match of a POSIX regular expression occurs within a given string.

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_like('Thailand', 'land');

Result:

t

In this case there was a match and so t (for true) was returned.

Here’s what happens when there’s no match:

SELECT regexp_like('Thailand', 'Land');

Result:

f

This time we got f for false. That’s because I used an uppercase L in the second argument, but the string only contains a lowercase l.

Here are some more basic examples:

\x
SELECT 
    regexp_like('Thailand', 'l.d') AS "l.d",
    regexp_like('Thailand', 'l..d') AS "l..d",
    regexp_like('Big fat cat', '\s.at') AS "\s.at",
    regexp_like('Big fat cat', '\s.Big') AS "\s.Big",
    regexp_like('Big fat cat', '\ABig') AS "\ABig",
    regexp_like('Big fat cat', 'Big\A') AS "Big\A",
    regexp_like('Big fat cat', '\Acat') AS "\Acat",
    regexp_like('Big fat cat', 'cat\A') AS "cat\A",
    regexp_like('Big fat cat', 'cat\Z') AS "cat\Z";

Result (using vertical output):

l.d    | f
l..d | t
\s.at | t
\s.Big | f
\ABig | t
Big\A | f
\Acat | f
cat\A | f
cat\Z | t

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_like('Thailand', 'Land', 'i');

Result:

t

This time the result is t for true. That’s because the search matched both uppercase and lowercase characters (i.e. both l and L).

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

OptionDescription
bRest of RE is a BRE
cCase-sensitive matching (overrides operator type)
eRest of RE is an ERE
iCase-insensitive matching (overrides operator type)
mHistorical synonym for n
nNewline-sensitive matching
pPartial newline-sensitive matching
qRest of RE is a literal (“quoted”) string, all ordinary characters
sNon-newline-sensitive matching (default)
tTight syntax (default)
wInverse partial newline-sensitive matching
xExpanded 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_like() function.