INSTR SQL Function

INSTR FUNCTION returns the position of the search string.

We generally use INSTR function within the SUBSTR function to calculate the position parameters in it.

SELECT INSTR(<value>, <value_to_match>, <direction>, <instance>) FROM Table_Name;

<direction> = 1 forward

<direction> = -1 reverse search

 <instance> = Time of occurrence

Examples: 

Get the position of the second occurrence of 'k' in the given string.

SELECT  INSTR('kiran kodavati','k',1,2) FROM DUAL;

Result : 7

Get the position of the third occurrence of 'a' in the given string from back.

SELECT  INSTR('kiran kodavati','a',-1,3) FROM DUAL;

Result: 4

Get the string starting the second occurrence of 'K' in the given string.

SELECT  SUBSTR('KIRAN KODAVATI',INSTR('KIRAN KODAVATI','K',1,2)) FROM DUAL;

Result : KODAVATI.