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 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 )
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:
Is the input string to search for the pattern.
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
string are different.
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
Specify the matching behavior of the function. The
match_parameter accepts the values listed in the following table:
|‘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 |
|‘x’||Ignores whitespace characters. By default, whitespace characters match themselves.|
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;
Here is the output:
RESULT ---------- 2
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.