In Oracle, the REGEXP_COUNT() function returns the number of times a pattern occurs in a source string.

Syntax

The syntax goes like this:

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

Where:

• source_char is a character expression that serves as the search value.
• pattern is the regular expression.
• position is a positive integer that specifies where to begin the search. The default is 1, meaning, start the search at the first character.
• match_param lets you change the default matching behaviour of the function. For example, it allows you to specify case-sensitivity, how multiple lines and spaces are dealt with, etc.

Example

Here’s a basic example of using REGEXP_COUNT() in Oracle:

SELECT 
    REGEXP_COUNT('My dog drinks beer', 'd.g')
FROM DUAL;

Result:

1

In this case there’s one match.

Regular expressions can be very powerful, and this example uses a very simple example. In order to use REGEXP_COUNT() effectively, you’ll need to know the correct pattern to use for the desired outcome. The examples on this page focus on the REGEXP_COUNT() function itself, not on regular expressions.

No Match

Here’s an example where there’s no match:

SELECT REGEXP_COUNT('My dogs like dregs', 't.g')
FROM DUAL;

Result:

0

There’s no match, so 0 is returned.

Multiple Matches

Here’s an example with multiple matches:

SELECT 
    REGEXP_COUNT('My dogs have dags', 'd.g')
FROM DUAL;

Result:

2

In this case, there are two matches.

Start Position

You can specify a starting position:

SELECT 
REGEXP_COUNT('My dogs have dags', 'd.g', 8)
FROM DUAL;

Result:

1

So here we only get one match. That is because the search doesn’t start until after the first occurrence (position 8).

Case Sensitivity

The REGEXP_COUNT() function follows Oracle’s collation determination rules, which define the collation to use when matching the string with the pattern.

However, you can explicitly specify case-sensitivity with the optional fourth argument. When you do this, it overrides any case-sensitivity or accent-sensitivity of the determined collation.

You can specify i for case-insensitive matching and c for case-sensitive matching.

Here’s an example:

SELECT 
    REGEXP_COUNT('My Cats', 'c.t', 1) AS "Default",
    REGEXP_COUNT('My Cats', 'c.t', 1, 'i') AS "Case Insensitive",
    REGEXP_COUNT('My Cats', 'c.t', 1, 'c') AS "Case Sensitive"
FROM DUAL;

Result:

   Default    Case Insensitive    Case Sensitive 
__________ ___________________ _________________ 
         0                   1                 0

Here, my collation is case-sensitive. The other two strings were forced to a case-insensitive and case-sensitive matching respectively.

Null Arguments

With the exception of the fourth argument, providing null for an argument results in null:

SET NULL 'null';
SELECT 
    REGEXP_COUNT(null, 'c.t', 1, 'i') AS "1",
    REGEXP_COUNT('Cat', null, 1, 'i') AS "2",
    REGEXP_COUNT('Cat', 'c.t', null, 'i') AS "3",
    REGEXP_COUNT('Cat', 'c.t', 1, null) AS "4"
FROM DUAL;

Result:

      1       2       3    4 
_______ _______ _______ ____ 
   null    null    null    0

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Wrong Number of Arguments

Passing no arguments to the function, or too few, results in an error:

SELECT REGEXP_COUNT()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT REGEXP_COUNT()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

The same applies when we pass too many arguments:

SELECT REGEXP_COUNT('Cat', 'c.t', 1, 'i', 'oops')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT REGEXP_COUNT('Cat', 'c.t', 1, 'i', 'oops')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action:

More Information

The REGEXP_COUNT() function (as well as Oracle’s other implementation of regular expressions) conforms with the IEEE Portable Operating System Interface (POSIX) regular expression standard and to the Unicode Regular Expression Guidelines of the Unicode Consortium.

See the Oracle documentation for more information and examples of the REGEXP_COUNT() function.