Sunday, 25 August 2013

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

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

ShareThis