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