Oracle REGEXP_COUNT() Function

Oracle REGEXP_COUNT() is a built-in function that returns the number of occurrences of a given regular expression pattern in a source string.

Syntax of Oracle REGEXP_COUNT()

Here is the syntax of the Oracle REGEXP_COUNT() function:

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

Parameters

source_char

Required. The string to be searched. It can be any of the following data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

pattern

Required. The regular expression pattern. It can be any of the following data types: CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes.

position

Optional. An integer indicating the starting position of the search. The default is 1.

match_param

Optional. The mode of matching to be performed. It can be VARCHAR2 or CHAR. You can change the default search behavior by using one or more of the following characters:

• 'i' specifies case-insensitive matching, even if the specified collation order is case-sensitive.
• 'c' specifies case-sensitive and accent-sensitive matching, even if the specified collation order is case-insensitive or accent-insensitive.
• 'n' allows the dot (.) to match newline characters. If you omit this parameter, the dot does not match newline characters.
• 'm' treats the source string as a multiline string. Oracle interprets the caret (^) and dollar sign ($) as the beginning and end of any line within the source string, not just the beginning or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single-line string.
• 'x' ignores white space characters. By default, white space characters match themselves.

Return Value

The Oracle REGEXP_COUNT() function returns an integer, which is the number of occurrences of the given regular expression pattern in a source string.

If the source string does not contain any matches for the regular expression pattern, REGEXP_COUNT() returns 0.

If any of the first three parameters is NULL, REGEXP_COUNT() returns NULL.

Oracle REGEXP_COUNT() Example

Here are several examples that demonstrate how to use the Oracle REGEXP_COUNT() function.

Basic Usage

The following statement tries to search for the regular expression (a|b)c in the string ABCabacbcabc, and returns the number of occurrences:

SELECT
    REGEXP_COUNT('ABCabacbcabc', '(a|b)c') "Result"
FROM dual;

Output:

   Result
_________
        3

In ABCabacbcabc, there are three matches with (a|b)c: ac, bc, and bc.

Starting Position

The Oracle REGEXP_COUNT() function allows you to specify the starting position of the search:

SELECT
    REGEXP_COUNT('ABCabacbcabc', '(a|b)c', 7) "Result"
FROM dual;

Output:

   Result
_________
        2

In this example, the search starts from the 7th position, so there are only two matching strings: bc and bc.

Matching Parameter

The Oracle REGEXP_COUNT() function allows you to specify matching parameters to change the behavior of the search. Oracle performs a case-sensitive search by default. If you want to perform a case-insensitive search, use the i parameter.

SELECT
    REGEXP_COUNT('ABCabacbcabc', '(a|b)c', 1, 'i') "Result"
FROM dual;

Output:

   Result
_________
        4

In this example, there are four matching strings: BC, ac, bc, and bc.

No Match

If the source string doesn’t have any matches with the regular expression pattern, REGEXP_COUNT() returns 0.

SELECT
    REGEXP_COUNT('ABCabacbcabc', '[0-9]') "Result"
FROM dual;

Output:

   Result
_________
        0

NULL Parameter

If any of the first three parameters is NULL, REGEXP_COUNT() returns NULL.

SET NULL 'NULL';
SELECT
    REGEXP_COUNT(NULL, 'a') "Result1",
    REGEXP_COUNT('a', NULL) "Result2",
    REGEXP_COUNT('a', 'b', NULL) "Result3"
FROM dual;

Output:

   Result1    Result2    Result3
__________ __________ __________
      NULL       NULL       NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

The Oracle REGEXP_COUNT() function is a built-in function that returns the number of times a given regular expression pattern appears in a source string.