REGEXP_COUNT searches a string for a regular expression pattern and returns the number of times the pattern occurs.
Syntax
INTEGER REGEXP_COUNT
(
srcstr TEXT,
pattern TEXT,
position DEFAULT 1,
modifier DEFAULT NULL
)Parameters
| Parameter | Type | Default | Description |
|---|---|---|---|
srcstr | TEXT | — | The string to search. |
pattern | TEXT | — | The regular expression pattern to search for. |
position | INTEGER | 1 | The character position in srcstr at which to start searching. |
modifier | TEXT | NULL | One or more flags that control pattern matching behavior. |
For the full list of supported modifiers, see Pattern Matching in the PostgreSQL documentation.
Examples
Count occurrences starting from position 1
The following example counts how many times the letter i appears in 'reinitializing', starting from the first character:
polardb=# SELECT REGEXP_COUNT('reinitializing', 'i', 1) FROM DUAL;
regexp_count
--------------
5
(1 row)Count occurrences starting from position 6
To start counting from a position other than the beginning, specify the position parameter. This example counts occurrences of i starting from position 6, so matches before that position are excluded:
polardb=# SELECT REGEXP_COUNT('reinitializing', 'i', 6) FROM DUAL;
regexp_count
--------------
3
(1 row)