A Quick Look at PostgreSQL’s REGEXP

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:

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:

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:

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:

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

Database.Guide

A Quick Look at PostgreSQL’s REGEXP_LIKE() Function

Example

Add a Flag

More About POSIX Regular Expressions in PostgreSQL