INSTR

Oracle INSTR function
The Oracle function instr returns an integer indicating the position of the character in string that is the first character of this occurrence.

This function has the following syntax:

instr(string, substring [,position [,occurrence]])

with:
string: the string that is searched.
substring: the substring which we are looking for in the string
position: The position from which we start the search (an integer value). If position is negative, then Oracle counts and searches backward from the end of string. If omitted, this defaults to 1.
occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive. If this is omitted, this defaults to 1.

Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype
If no value is found, the instr will return the value 0.

Examples

INSTR('CORPORATE FLOOR','OR', 3, 2) = 14
INSTR('CORPORATE FLOOR','OR', -3, 2) = 2
INSTR('ab ab ab','ab') = 1
INSTR('ab ab ab','ab',1,2) = 4
INSTR('ab ab ab','ab',2,2) = 7
INSTR('abcabcabcdef','de') = 7