Oracle REGEXP_COUNT :

In my previous articles, I have given the idea about different REGEXP functions and examples of REGEXP_LIKE function. In this article, I will try to give idea about another regular expression function, which is Oracle REGEXP_COUNT Examples. The Oracle REGEXP_COUNT function is used to count the occurrences of the pattern in the string. There are so many scenarios where user needs to calculate the occurrences in the string. There are so many situations where user need to calculate the count of different operators like comma or pipe operator. This function was introduced in Oracle 11g and it allows us to count the number of times a pattern or a substring occurs in the source string. This is very different from the plain COUNT function which is a column level function, whereas, REGEXP_COUNT is a cell level function which operates on each cell individually.

The Oracle REGEXP_COUNT Function is used to calculate the number of occurrences of regular expression in the string.

Syntax of REGEXP_COUNT:

REGEXP_COUNT (string, pattern, [position], [Match_parameter] );

The syntax of REGEXP_COUNT contains different options.

1. String: String is any string from which user needs to check specific pattern.

2. Pattern: The pattern is any specific pattern which user needs to search for count

3. Position: The position is any number from which user needs to start checking the pattern. The position should be any integer number, which is optional parameter. By default, it will start from character number one.

4.Match_parameter: Some match parameters are used in REGEXP_COUNT for specific purpose. The following parameters are important in case of REGEXP_COUNT function.

REGEXP_COUNT function always returns the number value.

• c: Case-sensitive matching.
• i: Case-insensitive matching.
• m: Treat the source string as multiple lines. The function interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.
• n: New-line character is among the characters matched by a period (the wildcard character). By default, it is not.
• x: Ignore whitespace characters.

These are above important parameters used in REGEXP_COUNT function. Now let us check different useful examples of REGEXP_COUNT function.

Things to remember:

1.If user has entered the conflicting values in match parameter then Oracle REGEXP_COUNT function always uses the last value.

2.If user removes the match_parameter then it should use the NLS_SORT parameter with case sensitive search.

3.If the REGEXP_COUNT function does not find any occurrence of pattern, it will return 0.

REGEXP_COUNT Examples:

The REGEXP_COUNT function is used to find the count of the specific pattern in the string. Let us take some useful examples of REGEXP_COUNT function.

Example 1: Count of Simple string pattern

If user wants to count, the simple string pattern from specific statement then REGEXP_COUNT function is useful.

Query:

SELECT

regexp_count(‘I love SQL.SQL is lovely language’, ‘SQL’)

from dual;

Output:

REGEXP_COUNT(‘ILOVESQL.SQLISLOVELYLANGUAGE’,’SQL’)

————————————————–

2    

Remember that the output this function searches the string with case sensitive.If you change the string SQL from uppercase to lowercase letter then the Answer of above query is 0.

Query:

SELECT

regexp_count(‘I love SQL.SQL is lovely language’, ‘sql’)

from dual;

Output:

REGEXP_COUNT(‘ILOVESQL.SQLISLOVELYLANGUAGE’,’SQL’)

————————————————–

0

Example 2: Count the Number of words from the statement in SQL (\w+ Option)

User can count the words from the SQL statement using REGEXP_COUNT function.

Query:

SELECT

regexp_count(‘I love SQL.SQL is lovely language’, ‘\w+’)

from dual;

Output:

REGEXP_COUNT(‘I LOVE SQL.SQL IS LOVELY LANGUAGE’,’\W+’)

————————————————–

7

Example 3 :  Counting the number of digits in the statement  

User can be able to count the number of digits from SQL statement using \d option of REGEXP_COUNT function.

Query:

SELECT

regexp_count(‘I love SQL.SQL is lovely language 114 7’, ‘\d’)

from dual;

Output:                

REGEXP_COUNT(‘ILOVESQL.SQLISLOVELYLANGUAGE1147′,’\D’)

—————————————————–

4

There are 4 digits used in Statement.

Example 4: Counting the number of vowels from the Statement

User can count the number of vowels from the statement using REGEXP_Count function.

Query:

SELECT

regexp_count(‘I love SQL.SQL is lovely language 114 7’, ‘[aeiou]’)

FROM dual;

Output:

REGEXP_COUNT(‘I LOVE SQL.SQL IS LOVELY LANGUAGE 1147′,'[AEIOU]’)

———————————————————-

9

The above statement contains 9 vowels.

Example 5: Count the specific pattern with case sensitivity off

User can use i option to remove the case sensitivity from the REGEXP_COUNT function.

Query:

SELECT

regexp_count(‘I love SQL.SQL is Lovely Language 114 7’, ‘[l]’,1,’i’)

FROM dual;

Output:

REGEXP_COUNT(‘ILOVESQL.SQLISLOVELYLANGUAGE1147′,'[L]’,1,’I’)

————————————————————

6

Example 6: Word count with specific pattern 

The total number of words in the source string can be found by using the below statement. The search pattern ‘[^ ]’checks for characters other than the space character.

SELECT

regexp_count(‘I love SQL.SQL is Lovely Language 114 7’, ‘[^ ]+’,1)

FROM dual;

Output:

REGEXP_COUNT(‘ILOVESQL.SQLISLOVELYLANGUAGE1147′,'[^]+’,1)

———————————————————

8

Example 7: Counting the characters from the Statement   

Using REGEXP_COUNT function user can count the number of character in the statement.

Query:

SELECT

regexp_count(‘I love SQL.SQL is Lovely Language 114 7’, ‘(.)’,1)

FROM dual;

Output:

REGEXP_COUNT(‘ILOVESQL.SQLISLOVELYLANGUAGE1147′,'(.)’,1)

——————————————————–

39

Example 8: Counting the commas in the statement

User can count the commas from the given statement using REGEXP_COUNT function.

Query:

SELECT

regexp_count(‘I love SQL,SQL is Lovely, Language 114 7’, ‘,’,1)

FROM dual;

Output:

REGEXP_COUNT(‘ILOVESQL,SQLISLOVELY,LANGUAGE1147′,’,’,1)

——————————————————-

2

Example 9 : Calculate the completion of statement or dots

The syntax is bit different to calculate the number of dots in the statement.  User needs to use ‘\.’ to calculate the dots in the given statement.

Query:

SELECT

regexp_count(‘I love SQL.SQL is Lovely Language.’, ‘\.’,1)

FROM dual;

Output:

REGEXP_COUNT(‘ILOVESQL.SQLISLOVELYLANGUAGE.’,’\.’,1)

—————————————————-

2