Oracle PL/SQL Instr Function

The Instr function returns the location of a substring in a string.

The Instr Function syntax:

instr( string1, string2 [, start_position [, n_appearance ] ] )

String1 is the string to search.

String2 is the substring to search for in string1.

Start_position is heading into string1 where the search will start. If omitted, it defaults to 1. If the function is negative, the start_position counts back the number of characters from the start_position end of string1 and Searches by the start of string1.

N_appearance is the n appearance of string2. If omitted, it defaults to 1.

Example:

select instr('Learn oracle lesson', 'e') from dual; 
would return 2

select instr('Learn oracle lesson', 'e',1,1) from dual;
would return 2

select instr('Learn oracle lesson', 'e',1,2) from dual;
would return 12

select instr('Learn oracle lesson', 'e',1,3) from dual;
would return 15

select instr('Learn oracle lesson', 'e',-5,2) from dual;
would return 12