Skip to content

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;
ENAMEPOSITION
SMITH0
ALLEN1
WARD2
JONES0
MARTIN2
BLAKE3
CLARK3
SCOTT0
KING0
TURNER0
ADAMS1
JAMES2
FORD0
MILLER0
SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'a',1,1,0,'c') position
FROM EMP;

Run above queries to get better understanding of REGEXP_INSTR syntax.

ENAMEPOSITION
SMITH0
ALLEN0
WARD0
JONES0
MARTIN0
BLAKE0
CLARK0
SCOTT0
KING0
TURNER0
ADAMS0
JAMES0
FORD0
MILLER0

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;
ENAMEPOSITION
SMITH0
ALLEN5
WARD0
JONES0
MARTIN6
BLAKE0
CLARK0
SCOTT0
KING0
TURNER0
ADAMS0
JAMES0
FORD0
MILLER0

3. Find the position of string that contains ER

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'ER') position
FROM EMP;
ENAMEPOSITION
SMITH0
ALLEN0
WARD0
JONES0
MARTIN0
BLAKE0
CLARK0
SCOTT0
KING0
TURNER5
ADAMS0
JAMES0
FORD0
MILLER5

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

SELECT ENAME, 
       REGEXP_INSTR(ENAME, 'T{2}') position
FROM EMP;
ENAMEPOSITION
SMITH0
ALLEN0
WARD0
JONES0
MARTIN0
BLAKE0
CLARK0
SCOTT4
KING0
TURNER0
ADAMS0
JAMES0
FORD0
MILLER0

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

SELECT ENAME, 
       REGEXP_INSTR(ENAME, '([TL])\1') position
FROM EMP;
ENAMEPOSITION
SMITH0
ALLEN2
WARD0
JONES0
MARTIN0
BLAKE0
CLARK0
SCOTT4
KING0
TURNER0
ADAMS0
JAMES0
FORD0
MILLER3

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;
ENAMEPOSITION
SMITH0
ALLEN0
WARD0
JONES0
MARTIN0
BLAKE0
CLARK0
SCOTT0
KING0
TURNER0
ADAMS2
JAMES0
FORD0
MILLER0

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;
ENAMEPOSITION
SMITH0
ALLEN0
WARD0
JONES0
MARTIN0
BLAKE1
CLARK1
SCOTT0
KING0
TURNER0
ADAMS1
JAMES0
FORD0
MILLER0

Published inOracle PL SQL