Oracle SQL: the Essential Reference

INSTR

INSTR(string1, string2[, n[, m]])

Searches string1 to find string2 and returns the character position in string1 where string2 begins.

Parameter

string1

Specifies the string you want to search.

string2

Specifies the string you want to find.

n

Specifies the character position from which the search should begin. The default is to begin searching from the first character, which is position 1. Use negative values to specify the starting position relative to the right end of string1, rather than the left.

m

Specifies which occurrence of string2 you want to find (if it occurs more than once). The default is to find the first occurrence.

Examples

SQL> SELECT INSTR('easy come, easy go','easy') FROM dual;

INSTR('EASYCOME,EASYGO','EASY')
-------------------------------
                              1

SQL> SELECT INSTR('easy come, easy go','easy',1,2) FROM dual;

INSTR('EASYCOME,EASYGO','EASY',1,2)
-----------------------------------
                                 12