Oracle REGEXP_COUNT function.

Preamble

Oracle/PLSQL REGEXP_COUNT function calculates the number of occurrences of the template in the string. This function, introduced in Oracle 11g, allows you to count the number of times a substring occurs in a string using a regular expression pattern matching.

Oracle/PLSQL syntax REGEXP_COUNT function

REGEXP_COUNT( string_id, pattern_id [, start_position_id [, atch_parameter_id ] ] ) 

Parameters and arguments

• string_id – A search line. String can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
• pattern_id – Template. Regular expression for comparison. It can be a combination of the following values:

• start_position_id – It’s optional. This is the position on the line from which the search will begin. If this option is omitted, by default it is 1, which is the first position in the line.
• match_parameter_id – It’s optional. It allows you to change the matching behavior for the function REGEXP_COUNT. This can be a combination of the following values:

The function REGEXP_COUNT returns a numerical value.

If there are conflicting values for match_parameter, the REGEXP_COUNT function will use the last value.

If the REGEXP_COUNT function does not detect any pattern occurrence, it will return 0.

REGEXP_COUNT function in the following versions of Oracle / PLSQL

Oracle 12c, Oracle 11g

Example of a single symbol match

Let’s look at the simplest example. Let’s calculate how many times the ‘a’ character appears in a string.
For instance:

SELECT REGEXP_COUNT ('Aller Anfang ist schwer', 'a')
FROM dual;

-Result: 1

This example will return 1 because it counts the number of ‘a’ occurrences in a string. Since we haven’t specified the match_parameter value, the REGEXP_COUNT function will perform a case sensitive search, which means that the ‘A’ character will not be included into the count.

If we wanted to include both ‘a’ and ‘A’ in our result and perform a case insensitive search, we would change our query as follows:

SELECT REGEXP_COUNT ('Aller Anfang ist schwer', 'a', 1, 'i')
FROM dual;

-Result: 3

Now, since we have provided start_position = 1 and match_parameter = ‘i’, the query will return 3 as a result. This time the values ‘a’ and ‘A’ will be included into the result.

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

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

This query will count the number of ‘a’ or ‘A’ values in the last_name field from the contacts table.

Example of a multi-character match

Let’s see how we will use the REGEXP_COUNT function to match a multi-character pattern.
For example:

SELECT REGEXP_COUNT ('Gute Saat, gute Ernte', 'gute', 1, 'i')
FROM dual;

-Result: 2

In this example, we will return the number of times the word ‘gute’ appears in the string. The search is case-insensitive, so the result is 2 entries.

SELECT REGEXP_COUNT ('Gute Saat, gute Ernte', 'gute', 5, 'i')
FROM dual;

-Result: 1

In this example, the number of times the word ‘gute’ appears in the string starting from position 5 will be returned. In this case the result is 1 because it will skip the first 4 characters in the string before searching for the template.

Now let’s see how we are going to use the REGEXP_COUNT function with the table column and search for several characters. For example:

SELECT REGEXP_COUNT (other_comments, 'the', 1, 'i')
FROM contacts;

-Result: 3

In this example, we will count the number of times “the” appears in the other_comments field in the contacts table.

This is an example of comparing several alternatives

The next example we’ll look at involves using the | pattern. | pattern is used as an “OR” to specify multiple alternatives. For example:

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

-Result: 3

This example will return 3 because it counts the number of vowels (a, e, i, o or u) in the string ‘AeroSmith’. Since we didn’t specify a match_parameter value, the REGEXP_COUNT function will perform case sensitive searches, which means that ‘A’ in ‘AeroSmith’ will not be counted.

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

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

-Result: 4

Now, since we have specified start_position = 1 and match_parameter = ‘i’, the query will return as result 4. This time ‘A’ in ‘AeroSmith’ will be included into the score.

Now let’s see how we will use this function with the column.

So, suppose we have a contact table with the following data:

Now let’s start the next request:

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

The results to be returned by the request:

About Enteros

​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.