The REGEXP_COUNT function searches a string for a regular expression and returns the number of times that the regular expression occurs.

Syntax

INTEGER REGEXP_COUNT
(
  srcstr    TEXT,
  pattern   TEXT,
  position  DEFAULT 1
  modifier  DEFAULT NULL
)        

Parameters

Parameter Description
srcstr The string to search.
pattern The regular expression for which REGEXP_COUNT will search.
position An integer value that indicates the position in the source string at which REGEXP_COUNT will start searching. The default value is 1.
modifier The values that control the pattern matching behavior. The default value is NULL.
Note For a complete list of the modifiers supported by PolarDB-O, see the PostgreSQL core documentation available at: PostgreSQL.

Examples

In the following example, REGEXP_COUNT returns the number of times the letter i is used in the string 'reinitializing':

polardb=# SELECT REGEXP_COUNT('reinitializing', 'i', 1) FROM DUAL;
 regexp_count 
--------------
            5
(1 row)        

In the first example, the command instructs REGEXP_COUNT to start counting in the first position. If you want to start counting in the sixth position, use the following command:

polardb=# SELECT REGEXP_COUNT('reinitializing', 'i', 6) FROM DUAL;
 regexp_count 
--------------
            3
(1 row)            

Then REGEXP_COUNT function returns 3, and the count does not include occurrences of the letter i that occur before the sixth position.