Oracle PL/SQL - INSTR function

In this tutorial we will explain how to use the Oracle PL/SQL INSTR function with syntax and examples

INSTR Function : 

Syntax : INSTR ('input_string',  'search_string',  start_position,  occurrence);

  • In Oracle PL/SQL INSTR function returns the position of the "search_string" in the given input_string.
  • input_string  and search string can be of data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB
  • return type is of data type NUMBER.
  • The first position in the input_string is treated as 1.
  • start_position is the position in the input_string from where the search will start.
  • If start_position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.
  • occurrence must be a positive integer.
  • If search_string is not found in input_string, then the INSTR function will return 0.
  • start_position and occurrence are optional and the default value is 1.

EXAMPLE 1 :

SELECT INSTR('DEVELOPERS ARENA','E',3, 2)
  "INSTRING_POSITION" FROM DUAL;
 
  INSTRING_POSITION
-------------------
                  8
EXAMPLE 2 :

SELECT INSTR('DEVELOPERS ARENA','E',-2, 3)
  "INSTRING_POSITION" FROM DUAL;
 
  INSTRING_POSITION
-------------------
                  4
EXAMPLE 3 :

SELECT INSTR('DEVELOPERS ARENA','E')
  "INSTRING_POSITION" FROM DUAL;
 
  INSTRING_POSITION
-------------------
                  2

Note: In the above example 3,since start_position and occurrence are omitted, they are defaulted to 1. Hence the search will happen from the first position and the will return the first occurrence of the letter "E".
Labels: , , , ,