Oracle SQL - Using 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 www  .  j  a  v  a  2s  .  c  om
----------
         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;-- from  ww w  .j ava2 s . c  o 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  www .  j a v a2 s .c  o  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;--  w ww .j  a v a2  s. c om

  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  w  w  . j  a  va  2 s.c o  m

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

SQL>

Related Topic