Oracle REGEXP_SUBSTR function

Preamble

The Oracle/PLSQL REGEXP_SUBSTR function is an extension of function SUBSTR. This function, represented in Oracle 10g, allows you to extract substring from a string using regular expression pattern matching.

Syntax of the Oracle/PLSQL function REGEXP_SUBSTR

REGEXP_SUBSTR( string_id, pattern_id [, start_position_id [, nth_appearance_id [, match_parameter_id [, sub_expression_id ] ] ] ] ] 

Parameters and function arguments

• string_id – A search line. It 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 – Optional. This is the position in the line from which the search will start. If this parameter is omitted, by default it is 1, which is the first position in the string.
• nth_appearance_id – Optional. This is the n-th view of the pattern in the string. If this option is omitted, it defaults to 1, which is the first entry of the template in the string. If you specify 0 for this parameter, all template entries in the string will be replaced.
• match_parameter_id – It’s optional. This allows you to change the compliance behavior for the REGEXP_REPLACE condition. This can be a combination of the following values:

• subexpression_id – Optional. Used when the template has subexpressions, and you want to specify which subexpression in the template is the target. This is an integer value between 0 and 9, indicating that the subexpression matches the template.

The REGEXP_SUBSTR function returns a string value.

If REGEXP_SUBSTR does not detect any pattern occurrence, it returns NULL.

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

REGEXP_SUBSTR function can be used in the following versions of Oracle / PLSQL

Oracle 12c, Oracle 11g, Oracle 10g

Example of a match in words

Let’s start by extracting the first word from the string.
For example:

SELECT REGEXP_SUBSTR ("Google is a great search engine.", '(\S*)(\s)')
FROM dual;
--Result: 'Google'

This example will return ‘Google’ because it will extract all characters without spaces as specified (\S*) and then the first character of the space specified (\s). The result will include both the first word and the space character after the word.

If you do not want to include a space in the result, we will change our example as follows:

SELECT REGEXP_SUBSTR ("Google is a great search engine.", '(\S*)')
FROM dual;
-Result: 'Google'

This example will return ‘Google’ without a space at the end.

If we need to find the second word in a line, we will change our function as follows:

SELECT REGEXP_SUBSTR ("Google is a great search engine.", '(\S*)(\s)', 1, 2)
FROM dual;
--Result: 'is '

This example will return ‘is ‘ with a space at the end of the line.
If we need to find the fourth word in the string, we will change our function as follows:

SELECT REGEXP_SUBSTR ("Google is a great search engine.", '(\S*)(\s)', 1, 4)
FROM dual;
--Result: 'great'

This example will return ‘great’ with a space at the end of the line.

Example of a number match

Let’s see how we will use the function REGEXP_SUBSTR to compare the pattern of digital characters.
For example:

SELECT REGEXP_SUBSTR ('2, 4, and 10 numbers for example', '\d').
FROM dual;
--Result: '2'

In this example, the first digit will be extracted from a line, as specified at \d. In this case, it will match number 2.

We could change our pattern to find a two-digit number.
For example:

SELECT REGEXP_SUBSTR ('2, 4, and 10 numbers for example', '(\d)(\d)')
FROM dual;
--Result: '10'

In this example, a number will be printed that has two digits as specified in (\d)(\d). In this case it will skip the numeric values 2 and 4 and return 10.

Let’s see how we will use the REGEXP_SUBSTR function with a table column and look for a two-digit number.
For example:

SELECT REGEXP_SUBSTR (address, '(\d)(\d)')
FROM contacts;

In this example, we are going to extract the first two-digit value from the address field in the contacts table.

Example of matching several alternatives

The following example, which we will look at, includes the use of | template. | template is used as an “OR” to specify several alternatives.
For example:

SELECT REGEXP_SUBSTR ('AeroSmith', 'a|e|i|o|u').
FROM dual;
--Result: 'e'

This example will return an ‘e’ because it looks for the first vowel (a, e, i, o or u) in the string. Since we didn’t specify a match_parameter value, the REGEXP_SUBSTR function will perform a case sensitive search, which means that ‘A’ in ‘AeroSmith’ will not be matched.

To perform a case-insensitive search, we will modify our query in the following way:

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

--Result: 'A'

Now since we have provided match_parameter = ‘i’, the query will return ‘A’ as a result. This time ‘A’ in ‘AeroSmith’ will be matched.

Now consider how you will use this function with a 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_SUBSTR (last_name, 'a|e|i|o|u', 1, 1, 'i') AS "First Vowel".
FROM contacts;

The results to be returned by the request:

Example of matches based on nth_occurrence parameter

The next example we will consider includes the nth_occurrence parameter. The nth_occurrence parameter allows you to choose from which occurrence of the template you want to extract the substring.

First occurrence

Let’s see how to extract the first occurrence of the template in a row.
For example:

SELECT REGEXP_SUBSTR ('AeroSmith', 'a|e|i|o|u', 1, 1, 'i')
FROM dual;
--Result: 'A'

This example will return ‘A’ because it retrieves the first vowel occurrence (a, e, i, o or u) in the string.

Second occurrence

Then we will select a template for the second line entry.
For example:

SELECT REGEXP_SUBSTR ('AeroSmith', 'a|e|i|o|u', 1, 2, 'i')
FROM dual;
--Result: 'e'

This example will return ‘e’ because it retrieves the second occurrence of a vowel (a, e, i, o or u) in a string.

The third occurrence

For example:

SELECT REGEXP_SUBSTR ('AeroSmith', 'a|e|i|o|u', 1, 3, 'i')
FROM dual;
--Result: 'o'

This example will return an ‘o’ because it retrieves the third vowel occurrence (a, e, i, o or u) in a string.

Oracle regular expression: extracting substring regexp substr

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.​