Oracle REGEXP_INSTR() Function By Practical Examples

Summary: in this tutorial, you will learn how to use the Oracle REGEXP_INSTR() function to search for a substring in a string using a regular expression pattern.

Introduction to Oracle REGEXP_INSTR() function

The REGEXP_INSTR() function enhances the functionality of the INSTR() function by allowing you to search for a substring in a string using a regular expression pattern.

The following illustrates the syntax of the REGEXP_INSTR() function:

REGEXP_INSTR(
    string,
    pattern, 
    position, 
    occurrence, 
    return_option, 
    match_parameter
)Code language: SQL (Structured Query Language) (sql)

The REGEXP_INSTR() function evaluates the string based on the pattern and returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the return_option argument. If the function does not find any match, it will return 0.

Here is the detail of each argument:

string (mandatory)

Is the string to search.

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 starting position in the string that the function begins the search.

The position defaults to 1, meaning that the function starts searching at the beginning of the string.

occurrence (optional)

Is a positive integer that determines for which occurrence of the pattern in the string the function should search. By default, the occurrence is 1, meaning that the function searches for the first occurrence of pattern.

return_option (optional)

The return_option can be 0 and 1. If return_option is 0, the function will return the position of the first character of the occurrence.

Otherwise, it returns the position of the character following the occurrence.

By default, return_option is 0.

match_parameter (optional)

Specify the default 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_INSTR() function examples

This regular expression matches any 11-12 digit phone number with optional group characters and (+) sign at the beginning:

(\+?( |-|\.)?\d{1,2}( |-|\.)?)?(\(?\d{3}\)?|\d{3})( |-|\.)?(\d{3}( |-|\.)?\d{4})
Code language: SQL (Structured Query Language) (sql)

Here is the explanation of the regular expression:

Oracle REGEXP_INSTR Sample Regular Expression

The following example uses the above regular expression to search for the first occurrence of a phone number in the string 'If you have any question please call 123-456-7890 or (123)-456-7891':

SELECT 
    REGEXP_INSTR(
        'If you have any question please call 123-456-7890 or (123)-456-7891',
        '(\+?( |-|\.)?\d{1,2}( |-|\.)?)?(\(?\d{3}\)?|\d{3})( |-|\.)?(\d{3}( |-|\.)?\d{4})') First_Phone_No
FROM
    dual;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

FIRST_PHONE_NO
---------------
            38
Code language: SQL (Structured Query Language) (sql)

To find the second phone number in the string, you pass the position and occurrence arguments to the REGEXP_INSTR() function as follows:

SELECT 
    REGEXP_INSTR(
        'If you have any question please call 123-456-7890 or (123)-456-7891',
        '(\+?( |-|\.)?\d{1,2}( |-|\.)?)?(\(?\d{3}\)?|\d{3})( |-|\.)?(\d{3}( |-|\.)?\d{4})',
        1,
        1) Second_Phone_No
FROM
    dual;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

SECOND_PHONE_NO
---------------
            54
Code language: SQL (Structured Query Language) (sql)

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

Was this tutorial helpful?