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