Oracle REGEXP_COUNT Function By Examples

Summary: in this tutorial, you will learn how to use the Oracle REGEXP_COUNT() function to get the number of times a pattern occurs in a string.

Introduction to Oracle REGEXP_COUNT() function

The REGEXP_COUNT() function complements the functionality of the REGEXP_INSTR() function by returning the number of times a pattern occurs in a string.

The basic syntax of the REGEXP_COUNT() function is the following:

REGEXP_COUNT(
    string,
    pattern, 
    position, 
    match_parameter
)
Code language: SQL (Structured Query Language) (sql)

The REGEXP_COUNT() function evaluates the string based on the pattern and returns the number of times that a pattern occurs in the string. If the function finds no match, it returns 0.

Here is the detail of each argument:

string (mandatory)

Is the input string to search for the pattern.

pattern (mandatory)

Is a regular expression to be matched.

The maximum size of the pattern is 512 bytes. The function will convert the type of the pattern to the type of the string if the types of pattern and string are different.

position (optional)

Is a positive integer that determines the beginning position in the string which the function starts the search. The default is 1, meaning that the function starts searching at the beginning of the string.

match_parameter (optional)

Specify the matching behavior of the function. The match_parameter accepts the values listed in the following table:

Value	Description
‘c’	Performs case-sensitive matching.
‘i’	Performs case-insensitive matching.
‘n’	Allows the period (.), which is the match-any-character character, to match the newline character. If you skip this parameter, then the period (.) does not match the newline character.
‘m’	The function treats the `string` as multiple lines. The function interprets the caret (`^`) and the dollar sign (`$`) as the start and end, respectively, of any line anywhere in the `string`, rather than only at the start or end of the entire `string`. If you skip this parameter, then function treats the source string as a single line.
‘x’	Ignores whitespace characters. By default, whitespace characters match themselves.

Oracle REGEXP_COUNT() function example

This example uses the REGEXP_COUNT() function to return the number of numbers in the string 'An apple costs 50 cents, a banana costs 10 cents.':

SELECT 
    REGEXP_COUNT('An apple costs 50 cents, a banana costs 10 cents.','\d+') result
FROM 
    dual;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

    RESULT
----------
        2
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle REGEXP_COUNT() function to search for a substring in a string using a regular expression pattern.

Was this tutorial helpful?