Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


The REGEXP_COUNT function returns the number of times a pattern occurs in a source string. The function evaluates strings using characters as defined by the input character set.

See also:


Return value

When a match is found, an INTEGER that indicates the number of occurrences of the pattern; otherwise 0.


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



The text expression for which the function searches.


The text expression for which the function searches. It is usually a text literal and can contain up to 512 bytes. If the data type of pattern is different from the data type of source_char, then the function converts pattern to the data type of source_char.

Note that the function ignores subexpression parentheses in pattern. For example, the pattern '(123(45))' is equivalent to '12345'. Also, the function interprets a period as a wildcard character that matches any character.


For a listing of the operators you can specify in pattern, see "Oracle Regular Expression Support" in Oracle Database SQL Language Reference.

A positive INTEGER indicating the character of source_char where the function should begin the search. The default is 1, meaning that the function begins the search at the first character of source_char. After finding the first occurrence of pattern, the function searches for a second occurrence beginning with the first character following the first occurrence.


A text expression that lets you change the default matching behavior of the function. You can specify one or more of the values shown in the following table.

Value Specifies
c Case-sensitive matching.
i Case-insensitive matching.
m Treat the source string as multiple lines. The function interprets ^ (caret) and $ (dollar sign) 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 A newline character is among the characters matched by a period (the wildcard character). By default, it is not.
x Ignores whitespace characters.

If you specify multiple contradictory values for this argument, then the function uses the last value. For example, if you specify 'ic', then the function uses case-sensitive matching. If you specify a character other than those shown above, then the function returns an error.

If you omit this optional argument, then the default case sensitivity is determined by the value of the NLS_SORT parameter; a period (.) does not match the newline character; and the source string is treated as a single line.