REGEXP_COUNT Function

This function is used to find the number of time a pattern had occurred in a string.

It takes input as string (characters) and returns output as integer .If no records found matching then the function returns 0

REGEXP_COUNT('source_char', 'pattern', position, 'match_param')

• Source_char is the input that we give. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

• Pattern is a text field which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

• Position: Tells the position from where searching needs to start. It is a positive integer

• Match_param :It can be any of these

'i' specifies case-insensitive matching

'c' specifies case-sensitive matching

'n' allows the period (.)

'm' treats the source string as multiple lines

'x' ignores white space characters.

For example

SELECT REGEXP_COUNT('123441441441', '441', 3, 'i') COUNT FROM DUAL;

     COUNT

----------

         3