Oracle SQL - Using REGEXP_INSTR function

  1. HOME
  2. Oracle SQL
  3. Function
  4. REGEXP_INSTR function

Introduction

The arguments for REGEXP_INSTR are: 

REGEXP_INSTR(String to search, Pattern, [Position, [Occurrence, [Return-option, [Parameters]]]])
  • 'String to search' refers to the string that will be searched for the pattern.
  • Pattern is the sought string, which will be expressed as an RE.

The first two arguments are not optional.

Demo

SQL>
SQL> SELECT REGEXP_INSTR('This is a test','a') position FROM dual;

  POSITION-- from   w  ww.  j  a va  2s.c o  m
----------
         9

SQL>

The letter "a" is found in the source string "This is a test."

Position is the place to begin the search for Pattern.

The default is 1.

Demo

SQL>
SQL> SELECT REGEXP_INSTR('This is a test','a',3) position
  2  FROM dual;-- ww  w . ja v a  2s. co m

  POSITION
----------
         9

SQL>

Here, we started in the third position of the search string, the first "a".

Position in REGEXP_INSTR cannot be negative - one cannot work from the right end of the string.

Occurrence refers to the first, second, third, etc.,

occurrence of the pattern in Source String is default to 1 (first).

Demo

SQL>
SQL> SELECT REGEXP_INSTR('a test, a test This is a test','a',1,2) position
  2  FROM dual;-- from   w w  w  .  ja  v a2 s  .co m

  POSITION
----------
         9

SQL>

This query illustrates searching for the second "a" starting at position 1.

Example 1: The default (0) beginning of the position where the pattern is found:

Demo

SQL>
SQL> SELECT REGEXP_INSTR('This is a test','a',1,2,0) position
  2  FROM dual;-- from w w  w .jav a2s .  co  m

  POSITION
----------
         0

SQL>

Example 2: The Return-option is set to 1 to indicate the end of the found pattern:

Demo

SQL>
SQL> SELECT REGEXP_INSTR('This is a test','a',1,2,1) position
  2  FROM dual;-- w  ww.  j  a  va 2 s . c o m

  POSITION
----------
         0

SQL>

Related Topic