A Quick Look at PostgreSQL’s REGEXP_COUNT() Function

In PostgreSQL, the regexp_count() function returns the number of times a given POSIX regular expression pattern matches in a given string.

We pass the string and pattern as arguments. We can also pass an argument to specify where to start the search. Additionally, we also have the option of specifying a flag that changes the function’s behaviour.

Syntax

The syntax goes like this:

regexp_count ( string text, pattern text [, start integer [, flags text ] ] )

So the first two arguments are required, and the following two are optional.

Example

Here’s a basic example to demonstrate:

SELECT regexp_count('A cat called Ash', 'A.');

Result:

2

Here, I checked to see how many A characters, followed by any other character were in the string (the dot matches any character).

Here’s what happens if we switch it around so that the dot comes first:

SELECT regexp_count('A cat called Ash', '.A');

Result:

1

This time there’s only one match. That’s because the first A of the string wasn’t a match, due to it not having any other character in front of it.

Change the Start Position

We can add a third argument to specify where to start the search within the string:

SELECT regexp_count('A cat called Ash', 'A.', 2);

Result:

1

Here, I specified that the search should start at position 2, and so this resulted in it missing the first A character.

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_count('A cat called Ash', 'A.', 1, 'i');

Result:

4

This time the result is 4. That’s because the search matched both uppercase and lowercase characters (i.e. both a and A).

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_count() function.