Oracle REGEXP_INSTR() Function

Oracle REGEXP_INSTR() is a built-in function that returns the index of a substring that matches a given regular expression pattern in a source string.

You can think of this function as an extension of INSTR() that supports regular expressions.

Oracle REGEXP_INSTR() Syntax

Here is the syntax for the Oracle REGEXP_INSTR() function:

REGEXP_INSTR(source_char, pattern)
REGEXP_INSTR(source_char, pattern, position, )
REGEXP_INSTR(source_char, pattern, position, occurrence)
REGEXP_INSTR(source_char, pattern, position, occurrence, return_opt)
REGEXP_INSTR(source_char, pattern, position, occurrence, return_opt, match_param)
REGEXP_INSTR(source_char, pattern, position, occurrence, return_opt, match_param, subexpr)

Parameters

source_char

Required. The string to search. It can be of any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

pattern

Required. The regular expression pattern. It can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can have a maximum length of 512 bytes.

position

Optional. An integer that indicates the starting position for the search. Defaults to 1.

occurrence

Optional. An integer that indicates which occurrence to return. Defaults to 1.

return_opt

Optional. Specifies which position index to return. If 0, returns the position index of the first character of the matching substring. If 1, returns the position index of the character immediately after the matching substring. Defaults to 0.

match_param

Optional. The pattern-matching behavior to use. It can be of type VARCHAR2 or CHAR. You can use this parameter to change the default search behavior. You can use one or more of the following characters:

  • 'i' specifies case-insensitive matching, even if the sorting rules for the determined collation are case-sensitive.
  • 'c' specifies case-sensitive and accent-sensitive matching, even if the sorting rules for the determined collation are not case-sensitive or accent-sensitive.
  • 'n' allows the period (.) (match any character) to match newline characters. If this parameter is omitted, the period does not match newline characters.
  • 'm' treats the source string as multiline. Oracle interprets the caret (^) and dollar sign ($) as the beginning and end of any line within the source string, not just the beginning or end of the entire source string. If this parameter is omitted, Oracle treats the source string as single-line.
  • 'x' ignores whitespace characters. By default, whitespace characters match themselves.
subexpr

Optional. An integer from 0 to 9. 0 represents the entire regular expression, and 1-9 represent groups within the regular expression. Defaults to 0.

Return Value

The Oracle REGEXP_INSTR() function returns an integer that is the index of a substring that matches the given regular expression pattern in a source string.

If there is no match between the source string and the regular expression pattern, REGEXP_INSTR() returns 0.

If any of the parameters except for match_param are NULL, REGEXP_COUNT() returns NULL.

Oracle REGEXP_INSTR() Example

Here are some examples demonstrating the usage of the Oracle REGEXP_INSTR() function.

Basic Usage

The following statement is used to find the index of the first occurrence of consecutive digits in the string '123abc456def'.

SELECT
    REGEXP_INSTR('abc123def456', '\d+')
FROM dual;

Output:

   REGEXP_INSTR('ABC123DEF456','\D+')
_____________________________________
                                    4

Starting Position

The following statement is used to find the index of the first occurrence of consecutive digits in the string '123abc456def', starting the search from the 7th character.

SELECT
    REGEXP_INSTR('abc123def456', '\d+', 7)
FROM dual;

Output:

   REGEXP_INSTR('ABC123DEF456','\D+',7)
_______________________________________
                                     10

Nth Occurrence

The following statement is used to find the index of the second occurrence of consecutive digits in the string '123abc456def'.

SELECT
    REGEXP_INSTR('abc123def456', '\d+', 1, 2)
FROM dual;

Output:

   REGEXP_INSTR('ABC123DEF456','\D+',1,2)
_________________________________________
                                       10

Index Type

The following statement is used to find the index after the first occurrence of consecutive digits in the string '123abc456def'.

SELECT
    REGEXP_INSTR('abc123def456', '\d+', 1, 1, 1)
FROM dual;

Output:

   REGEXP_INSTR('ABC123DEF456','\D+',1,1,1)
___________________________________________
                                          7

Matching Parameter

The Oracle REGEXP_INSTR() function allows you to specify matching parameters to alter the behavior of the search. Oracle performs a case-sensitive search by default, use the i parameter if you want a case-insensitive search.

SELECT
    REGEXP_INSTR('ABC123def456', '[a-z]+') "Result1",
    REGEXP_INSTR('ABC123def456', '[a-z]+', 1, 1, 0, 'i') "Result2"
FROM dual;

Output:

   Result1    Result2
__________ __________
         7          1

NULL Parameters

If any parameter other than match_param is NULL, REGEXP_COUNT() will return NULL.

SET NULL 'NULL';
SELECT
    REGEXP_INSTR(NULL, 'a') "Result1",
    REGEXP_INSTR('a', NULL) "Result2",
    REGEXP_INSTR('a', 'b', NULL) "Result3",
    REGEXP_INSTR('a', 'b', 1, NULL) "Result4",
    REGEXP_INSTR('a', 'b', 1, 1, NULL) "Result5",
    REGEXP_INSTR('a', 'b', 1, 1, 0, NULL) "Result6",
    REGEXP_INSTR('a', 'b', 1, 1, 0, 'i', NULL) "Result7"
FROM dual;

Output:

   Result1    Result2    Result3    Result4    Result5    Result6    Result7
__________ __________ __________ __________ __________ __________ __________
      NULL       NULL       NULL       NULL       NULL          0       NULL

In this example, we use the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion

Oracle REGEXP_INSTR() is a built-in function that returns the index of a substring that matches a given regular expression pattern in a source string.