Purpose
REGEXP_COUNT() returns the number of occurrences of a regular expression in the source string.
Syntax
REGEXP_COUNT (source_char, pattern [, position [, match_param]])
Parameters
| Parameter | Description |
|---|---|
| source_char | A character expression that serves as the search value. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| pattern | The regular expression to find a substring to extract. It is usually a text literal and can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. Parentheses in the subexpression are ignored. For example, 12(34)5 is equivalent to 12345. |
| position | The position where the function begins searching for a match. This optional parameter is a positive integer. The default value is 1, which means that the function begins searching at the first character of source_char. |
| match_param | The default matching behavior of the function. This parameter is optional. It is a character expression of the VARCHAR2 or CHAR data type.
|
Return type
The return type is NUMBER.
Examples
The following example searches for commas (,) in abc,def,ghi starting from the first character and returns the number of commas.
obclient> SELECT REGEXP_COUNT('abc,def,ghi',',',1,'i') FROM DUAL;
+---------------------------------------+
| REGEXP_COUNT('ABC,DEF,GHI',',',1,'I') |
+---------------------------------------+
| 2 |
+---------------------------------------+
1 row in set
