Oracle / PLSQL: REGEXP_COUNT Function

The Oracle/PLSQL REGEXP_COUNT function counts the number of times that a pattern occurs in a string. This function, introduced in Oracle 11g, will allow you to count the number of times a substring occurs in a string using regular expression pattern matching.

REGEXP_COUNT( string, pattern [, start_position [, match_parameter ] ] )

Parameters :

The string to search. string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

The regular expression matching information. It can be a combination of the following:

Optional. It is the position in string where the search will start. If omitted, it defaults to 1 which is the first position in the string.

Optional. It allows you to modify the matching behavior for the REGEXP_COUNT function. It can be a combination of the following:

Returns

The REGEXP_COUNT function returns a numeric value.

• If there are conflicting values provided for match_parameter, the REGEXP_COUNT function will use the last value.
• If you omit the match_behavior parameter, the REGEXP_COUNT function will use the NLS_SORT parameter to determine if it should use a case-sensitive search, it will assume that string is a single line, and assume the period character to match any character (not the newline character).
• If the REGEXP_COUNT function does not find any occurrence of pattern, it will return 0.
• The REGEXP_COUNT function can be used in the following versions of Oracle/PLSQL: Oracle 12c, Oracle 11g

Example - Match on Single Character

SELECT REGEXP_COUNT ('dExams is a great resource', 'e')
FROM dual;

Result: 3

This example will return 3 because it is counting the number of occurrences of 'e' in the string. Since we did not specify a match_parameter value, the REGEXP_COUNT function will perform a case-sensitive search which means that the 'E' characters will not be included in the count.

If we wanted to include both 'e' and 'E' in our results and perform a case-insensitive search, we could modify our query as follows:

SELECT REGEXP_COUNT ('dExams is a great resource', 'e',1,'i')
FROM dual;

Result : 4

Now because we have provide a start_position of 1 and a match_parameter of 'i', the query will return 4 as the result. This time, both 'e' and 'E' values would be included in the count.

If we wanted to count the number of 't' in a column, we could try something like this:

SELECT REGEXP_COUNT (last_name, 't', 1, 'i') AS total
FROM contacts;

Example - Match on Multiple Characters

SELECT REGEXP_COUNT ('The example shows how to use the REGEXP_COUNT function', 'the', 1, 'i')
FROM dual;

Result: 2

This example will return the number of times that the word 'the' appears in the string. It will perform a case-insensitive search so it will return 2.

For example:

SELECT REGEXP_COUNT ('The example shows how to use the REGEXP_COUNT function', 'the', 4, 'i')
FROM dual;

Result: 1

This example will return the number of times that the word 'the' appears in the string starting from position 4. In this case, it will return 1 because it will skip over the first 3 characters in the string before searching for the pattern.

Example - Match on more than one alternative

The next example that we will look at involves using the | pattern. The | pattern is used like an "OR" to specify more than one alternative.

SELECT REGEXP_COUNT ('Anderson', 'a|e|i|o|u')
FROM dual;

Result: 2

This example will return 2 because it is counting the number of vowels (a, e, i, o, or u) in the string 'Anderson'. Since we did not specify a match_parameter value, the REGEXP_COUNT function will perform a case-sensitive search which means that the 'A' in 'Anderson' will not be counted.

We could modify our query as follows to perform a case-insensitive search as follows:

SELECT REGEXP_COUNT ('Anderson', 'a|e|i|o|u', 1, 'i')
FROM dual;

Result: 3

Now because we have provide a start_position of 1 and a match_parameter of 'i', the query will return 3 as the result. This time, the 'A' in 'Anderson' will be included in the count.

Now, let's quickly show how you would use this function with a column.

So let's say we have a contact table with the following data:

contact_id	last_name
1000	Anderson
2000	Smith
3000	Johnson

Now, let's run the following query:

SELECT contact_id, last_name, REGEXP_COUNT (last_name, 'a|e|i|o|u', 1, 'i') AS total
FROM contacts;

These are the results that would be returned by the query: