REGEXP_INSTR Oracle Regular Expression.

Learn how to find the position of a matching character or a matching string from the input string using INSTR function with example queries.

REGEXP_INSTR function is an extension of the INSTR function.

REGEXP_INSTR- search a string for a regular expression 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 no match is found, the function returns 0.

Below is the syntax for REGEXP_INSTR function

REGEXP_INSTR(<Source_string>, <Search_pattern>, <Start_position>, <nth_appearance>, <Return_option>, <Match_modifier>)

Source_string – is the string to search.

Search_pattern – Is a regular expression to be matched.

Start_position(optional)-Is a positive integer that determines start 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.

nth_appearance(optional)-It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first appearance of pattern in string.

Return_option(optional)-If a return_option of 0 is provided, the position of the first character of the occurrence of pattern is returned. If a return_option of 1 is provided, the position of the character after the occurrence of pattern is returned. If omitted, it defaults to 0.

Match_modifier(optional)-It allows you to modify the matching behavior for the REGEXP_INSTR function. It can be a combination of the following
‘c’-Perform case-sensitive matching.
‘i’-Perform case-insensitive matching

Example queries

1. find the position of the first ‘A’ character in a string (ENAME FROM EMP table)

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'A') position
FROM EMP;

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'A',1) position
FROM EMP;

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'A',1,1) position
FROM EMP;

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'A',1,1,0) position
FROM EMP;

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'A',1,1,0,'i') position -- i is case insensitive - it matches the pattern with upper and lowere case.
FROM EMP;

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'a',1,1,0,'i') position
FROM EMP;

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'A',1,1,0,'c') position -- c is case sensitive - it matches the pattern only in current matching case (either lower or upper)
FROM EMP;

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'a',1,1,0,'c') position
FROM EMP;

Run above queries to get better understanding of REGEXP_INSTR syntax.

2. find the position of a character that end with ‘N’ in a string (ENAME FROM EMP table)

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'N$') 
FROM EMP;

3. Find the position of string that contains ER

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'ER') position
FROM EMP;

4. Find the the position of string that contains exactly two letters T

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'T{2}') position
FROM EMP;

5. Find the the position of string with the double adjacent Letter

SELECT ENAME, 
       REGEXP_INSTR(ENAME, '([TL])\1') position
FROM EMP;

6. Find the the position of string that contain a letter in the range of ‘D’ and ‘G’, followed by the letter ‘A’

SELECT ENAME, 
        REGEXP_INSTR(ENAME, '[D-G]A') position
FROM EMP;

7. Find the position of string that contain a letter in the range of ‘A’ and ‘Z’,followed by any character followed by the letter ‘A’.

SELECT ENAME, 
       REGEXP_INSTR(ENAME, '[A-Z].A') position
FROM EMP;