Instr

INSTR is an SQL function that returns the position of a sub-string within a string (char or varchar2) value. One can specify a starting position and the number of occurrences. If the search is unsuccessful the function will return 0.

Syntax

The syntax is:

INSTR(string, substring, start_position, occurrences)

Examples

Find the character position of the '2' in the string:

SELECT INSTR('123', '2') FROM dual;
INSTR('123',2)
--------------
             2

Find the position of the second occurrence of the sub-string:

SELECT INSTR('ABCABC', 'ABC', 2) FROM dual;
INSTR('ABCABC','ABC',2)
-----------------------
                      4

Also see

• REGEXP INSTR‎, regular expression based equivalent.
• Substr