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

1 comment:

  1. This article was extremely helpful thank you. Don’t miss this article for more IQ Test Online. IQ tests provide insight into reasoning and memory.

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis