REGEXP_COUNT is one of the vital Numeric/Math functions of Oracle. It is much like the COUNT function which is used to get the Count of an expression. But the difference is that the REGEXP_COUNT is used to get the Count of a regular expression pattern in a string. The REGEXP_COUNT function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c and Oracle 11g.
Syntax:
REGEXP_COUNT( string, pattern, start_position, match_parameter )
Parameters:
string: It is used to specify the string to search.
pattern: It is used to specify the regular expression pattern to search for.
start_position: It is an optional parameter which is used to specify the position in string where the search will start. Its default value is 1, i.e, the first position. It also accepts negative value, and in that case it counts back from the end of string and then starts the search backwards towards the beginning of the string. However, whatever be the case the value of the position will be same as counted from the start, i.e, the first position belongs to the first character of the string only the start position is from the beginning or the end.
match_parameter: It is also an optional parameter which is used to modify the matching behavior for the function. It can take value/values from the following:
VALUE BEHAVIOUR ‘c’ To perform a case sensitive matching. ‘i’ To perform a case insensitive matching. ‘m’ To assume that the expression have multiple lines. ‘n’ To allow the period character (.) to match the newline character. ‘x’ To ignore the Whitespace characters.
Example 1:
SELECT REGEXP_COUNT ('HeLlO WoRlD', 'l')
FROM dual;
Output:
2
Explanation:
By default the value of the match_parameter is ‘c’, thus it performs a case sensitive match if no specific value is present for this parameter. Hence there are two occurrences of ‘l’ in the string, from the first position, and so the count is 2.
Example 2:
SELECT REGEXP_COUNT ('HeLlO WoRlD', 'l', 1, ‘i’)
FROM dual;
Output:
3
Explanation:
In this example the value of the start position is 1, i.e the search will begin from the first position, and the value of the match_parameter is ‘i’, thus it will perform a case insensitive match. Hence there are three occurrences of ‘l’ in the string, from the first position, and so the count is 3.
Example 3:
SELECT REGEXP_COUNT ('Hello ladies and hello gentlemen.', ‘hello’, 1, ‘i’)
FROM dual;
Output:
2
Explanation:
In this example the value of the start position is 1, i.e the search will begin from the first position, and the value of the match_parameter is ‘i’, thus it will perform a case insensitive match. Hence there are two occurrences of ‘hello’ in the string, from the first position, and so the count is 2.
Example 4:
SELECT REGEXP_COUNT ('Hello ladies and hello gentlemen', ‘hello’, 6, ‘i’)
FROM dual;
Output:
1
Explanation:
In this example the value of the start position is 6, i.e the search will begin from the sixth position, and the value of the match_parameter is ‘i’, thus it will perform a case insensitive match. Hence there is only one occurrence of ‘hello’ in the string, when counted from the sixth position, and so the count is 1.
Example 5:
SELECT REGEXP_COUNT ('HELLO World', 'a|e|i|o|u')
FROM dual;
Output:
1
Explanation:
By default the value of the match_parameter is ‘c’, thus it performs a case sensitive match if no specific value is present for this parameter. Hence there is only one occurrence of ‘a|e|i|o|u’ in the string, counted from the first position, and so the count is 1.
Example 6:
SELECT REGEXP_COUNT ('HELLO World', 'a|e|i|o|u', 1, ‘i’)
FROM dual;
Output:
3
Explanation:
In this example the value of the start position is 1, i.e the search will begin from the first position, and the value of the match_parameter is ‘i’, thus it will perform a case insensitive match. Hence there are three occurrences of ‘a|e|i|o|u’ in the string, counted from the first position, and so the count is 3.
Example 7:
SELECT REGEXP_COUNT (‘Hello ladies and hello gentlemen', 'a|e|i|o|u', 6, ‘i’) FROM dual;
Output:
9
Explanation:
In this example the value of the start position is 6, i.e the search will begin from the sixth position, and the value of the match_parameter is ‘i’, thus it will perform a case insensitive match. Hence there are nine occurrences of ‘a|e|i|o|u’ in the string, when counted from the sixth position, and so the count is 9.