The INSTR function in Oracle PL/SQL is a function that returns the position of a specified substring within a string. The syntax for the function is as follows:
INSTR(string, substring, [start_position], [nth_appearance]).
The string parameter is the string to search in, the substring parameter is the string to search for, the start_position parameter is an optional parameter that specifies the position within the string to start the search (default is 1), and the nth_appearance parameter is an optional parameter that specifies which occurrence of the substring to search for (default is 1). The function returns an integer that represents the position of the substring within the string.
INSTR syntax
INSTR(string , substring [, position [, occurrence]])
INSTR example
select instr('PL/SQL Reference', 'r') from dual;
Return value: 8
select instr('PL/SQL Reference', 'SQL') from dual;
Return value: 4
select instr('PL/SQL reference', 'r',1,1) from dual;
Return value: 8
select instr('PL/SQL reference', 'r',1,2) from dual;
Return value: 12