REGEXP_COUNT

Returns the number times a regular expression matches a string.

This function operates on UTF-8 strings using the default locale, even if the locale is set otherwise.

Syntax

REGEXP_COUNT ( string-expession, pattern [, position [, regexp-modifier ]... ] )

Parameters

string-expression

The VARCHAR or LONG VARCHAR expression to evaluate for matches with the regular expression specified in pattern. If string-expression is in the __raw__ column of a flex or columnar table, cast the string to a LONG VARCHAR before searching for pattern.

pattern

The regular expression to match against string-expression. The regular expression must conform with Perl regular expression syntax.

position

The number of characters from the start of the string where the function should start searching for matches. By default, the function begins searching for a match at the first (leftmost) character. Setting this parameter to a value greater than 1 begins searching for a match at the *n*th character you specify.

Default: 1

regexp-modifier

One or more single-character flags that modify how the regular expression pattern is matched to string-expression:

• b: Treat strings as binary octets, rather than UTF-8 characters.

• c (default): Force the match to be case sensitive.

• i: Force the match to be case insensitive.

• m: Treat the string to match as multiple lines. Using this modifier, the start of line (^) and end of line ($) regular expression operators match line breaks (\n) within the string. Without the m modifier, the start and end of line operators match only the start and end of the string.

• n: Match the regular expression operator (.) to a newline (\n). By default, the . operator matches any character except a newline.

• x: Add comments to regular expressions. The x modifier causes the function to ignore all un-escaped space characters and comments in the regular expression. Comments start with hash (#) and end with a newline (\n). All spaces in the regular expression to be matched in strings must be escaped with a backslash (\).

Examples

Count the number of occurrences of the substring an in the specified string (a man, a plan, a canal: Panama):

=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', 'an');
 REGEXP_COUNT
--------------
            4
(1 row)

Find the number of occurrences of the substring an, starting with the fifth character.

=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', 'an',5);
 REGEXP_COUNT
--------------
            3
(1 row)

Find the number of occurrences of a substring containing a lower-case character followed by an:

=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', '[a-z]an');
 REGEXP_COUNT
--------------
            3
(1 row

REGEXP_COUNT specifies the i modifier, so it ignores case:

=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', '[a-z]an', 1, 'i');

 REGEXP_COUNT
--------------
            4