Systems Engineering and RDBMS

REGEXP_COUNT in Oracle 11g

Posted by decipherinfosys on March 8, 2008

Oracle introduced regular expression functions in 10g version. REGEXP_INSTR, REGEXP_REPLACE and REGEXP_SUBSTR. These functions are similar in functionality that of standard string functions INSTR, REPLACE and SUBSTR respectively but with more powerful capabilities of pattern matching. Using regular expression functions, we can search, validate, remove unwanted text and/or replace characters from the string. It can be used for any character data including CLOB and NCLOB as well.

In one of our previous blog post, we had covered the usage of REGEXP_REPLACE function to remove unwanted characters from the string. In this blog post, we will talk about REGEXP_COUNT function. This functions returns the number of occurrences of the pattern in the source string. It takes four arguments.

• Source string: It is the source string from which we are going to find the occurrence of the pattern. This can be CLOB or NCLOB characters as well.
• Pattern: Is any string, pattern or text which will be found in the source string. Data type of pattern will be converted to data type of source string, if it is different than the source string.
• Position: It is a non zero positive value from which search should begin.
• Matching parameter: which controls the behavior of the search. It takes one or more value from
o I (ignore case)
o c – Case Sensitive
o m – Allows source string as multiple lines
o n – Allows period.
o x – Ignore white space characters

Now let us start with the some simple example. In following example, we are counting occurrence of ‘i’ in the source string. We are ignoring case in matching parameter.

SQL> SELECT REGEXP_COUNT(‘Decipher Information System’,’i’,1,’i’) FROM DUAL;


There are 3 incidents of the ‘i’ in the string. If we change the match parameter to ‘c’, occurrence will be only 2.

SQL> SELECT REGEXP_COUNT(‘Decipher Information System’,’i’,1,’c’) FROM DUAL;


Following is the example, where we are searching for pattern starting from the position 3.

SQL> SELECT REGEXP_COUNT(‘Cat in the hat is on the bat.’,’at’,4) FROM DUAL;


This function really helps to identify number of occurrences of the specific pattern in the source string. It reduce the long PL/SQL code to identify number of occurrences into one single function call.

Sorry, the comment form is closed at this time.

%d bloggers like this: