REGEXP_COUNT

REGEXP_COUNT searches a string for a regular pattern and returns the number of times the pattern occurs. If no match is found, the function returns 0.

The function evaluates strings using characters as defined by the input character set.

Return Value

NUMBER

Syntax

REGEXP_COUNT (source_char, pattern
              [, position
                 [, match_parameter ]
              ]
             )

Arguments

source_char is the text expression to search.

pattern is the string to search for. A period matches any character. For a list of operators, refer to the Oracle Database SQL Language Reference, Appendix D, "Oracle Regular Expression Support."

position is a nonzero integer indicating the character of source_char where the function begins the search. When position is negative, then the function counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the first character of source_char.

match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values:

• 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.

Example

REGEXP_COUNT('Mississippi', 'i', 1) searches the string Mississippi for the letter i, beginning the search at the first letter. It returns the value 4.