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 for Oracle, 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.