Instr

From Oracle FAQ
Jump to: navigation, search

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[edit]

The syntax is:

INSTR(string, substring, start_position, occurrences)

Examples[edit]

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[edit]