REGEXP_COUNT – Oracle 11g feature

Learn how to count the number of occurrences of the given sub-string present in the input string with example queries.

REGEXP_COUNT returns the number of occurrences of the regular expression in the string.

Syntax

REGEXP_COUNT(<Source_string>, <Search_pattern>, <Start_position>, <Match_modifier>)

string– is the string to search
Search_pattern-Is a regular expression to be matched
Start_position(optional)-Is a positive integer that determines start position in the string that the function begins the search.The position defaults to 1, meaning that the function starts searching at the beginning of the string.

Match_modifier(optional)-It allows you to modify the matching behavior for the REGEXP_INSTR function. It can be a combination of the following
‘c’-Perform case-sensitive matching.
‘i’-Perform case-insensitive matching.

The REGEXP_COUNT function returns a numeric value.

Example queries

Extract the count of digit 1 present in the given input string

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '1')
FROM dual;

Output

3

Extract the count of number of digits present in the given input string.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '(\d)')
FROM dual;

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '[0-9]')
FROM dual;

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '[[:digit:]]')
FROM dual;

Output

4

Extract the count of 2 digits number present in the given input string.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '(\d)(\d)')
FROM dual;

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '(\d){2}')
FROM dual;

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '[0-9]{2}')
FROM dual;

Output
2

Extract the count of character “i” presence in the given input string.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', 'i')
FROM dual;

Output
4

Extract the count of substring “is” presence in the given input string.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', 'is')
FROM dual;

Output
2

Extract the count of sub-string match on more than one alternative

The | pattern is used like an “OR” to specify more than one alternative.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', 'is|a|g')
FROM dual;

Output
7

Extract the Count of number of words present in the string.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '\w+')
FROM dual;

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '[^ ]+')
FROM dual;

Output
9

Extract the count of non word characters present in the string.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '\W+')
FROM dual;

Output
8

Extract the Count of number of non digit characters present in the string.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '\D')
FROM dual;

Output
45

Extract the Count of number of character present in the string.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '.')
 FROM dual;

Output
49

Extract the Count of number of vowels present in the string.

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '[aeiou]')
FROM dual;

Output
13

Extract the Count of number of alphabets present in the given input string

SELECT REGEXP_COUNT ('This is a oracle 11g and 12c substring expression', '[[:alpha:]]')
FROM dual;

Output

37