INSTR returns position of search string from forward and backward in Oracle

INSTR function is used to find position of string in search string from starting or ending point in Oracle

Syntax:

INSTR (string , substring [, position [, occurrence ] ])

Parameter 1: String is the value inwhich function look.
Parameter 2: Substring is the string value search in Parameter 1 String
Parameter 3: (Default 1 mention position return) If you use -1 then it will start from the end of the string.
Parameter 4: Occurrence used to find the position of 1st or 2nd or 3rd occurance.

Example: INST is used with different parameter to find the position


SQL> SELECT INSTR('THIS IS THE BEST THING','T',1) "Position" from dual;

Position
--------
       1

SQL> SELECT INSTR('THIS IS THE BEST THING','T',1,2) "Position" from dual;

Position
--------
       9

-- USe -1 third parameter, find position from backward.
SQL> SELECT INSTR('THIS IS THE BEST THING','T',-1) "Position" from dual;

Position
--------
      18

-- Use -1 third parameter indicate find position from backward 
SQL> SELECT INSTR('THIS IS THE BEST THING','T',-1,2) "Position" from dual;

Position
--------
      16

SQL> SELECT INSTR('THIS IS THE BEST THING','T',2) "Position" from dual;

Position
--------
       9

SQL> SELECT INSTR('THIS IS THE BEST THING','T',1,2) "Position Found" from dual;

Position
--------
       9

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.