Regexp_count function in Oracle 11g

In Oracle 11g, you can use the REGEXP_COUNT function. REGEXP_COUNT returns the number of times the mode appears in the source string, which is used as a supplement to the REGEXP_INSTR function. Note that although COUNT is a set function that operates on Row groups, REGEXP_COUNT is a single-row function that calculates each row separately.
The REGEXP_COUNT syntax is as follows:
REGEXP_COUNT (source_char, pattern [, position [, match_param])

REGEXP_COUNT returns the number of times that pattern appears in the source_char string. If no match is found, the function returns 0. The position variable tells Oracle where to start searching for the source string. The Count result is increased by 1 every time the mode appears after the start position.

The match_param variable supports the following values:

'I' is used for case-insensitive matching.
'C' is used for case-sensitive matching.
'N' allows periods (.) As wildcards to match line breaks. If this parameter is omitted, the line break does not match
'M' treats the source string as multiple rows. Oracle regards ^ and $ as the start and end of any row at any position in the source string, rather than the start or end of the entire source string. If this parameter is omitted, Oracle regards the source string as a row.
'X' ignores space characters. By default, space characters match themselves.

If multiple conflicting values are specified for match_param, Oracle uses the last value.
You can use REGEXP_COUNT to modify the LENGTH example in the previous section. You can use the following syntax

Select (LENGTH ('George ')-LENGTH (REPLACE ('George', 'ge', NULL)/LENGTH ('ge') AS Counter
From DUAL;
COUNTER
-------
2

Replace the following syntax to get the same result:
Select REGEXP_COUNT ('George ', 'ge', 1,' I ')
From DUAL;

Replacing LENGTH with REGEXP_COUNT also has the advantage of case-insensitive searches. Therefore, the preceding query can also be written as follows:
Select REGEXP_COUNT ('George ', 'ge', 1,' I ')
From DUAL;

REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, and REGEXP_COUNT functions are used only by the ability to develop regular expressions, while regular expressions reflect your needs. As shown in the examples in this chapter,

You can use these functions to modify the display of existing data, find complex modes, and return strings in modes.