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:

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