What is INSTR & SUBSTR in Oracle ?

SUBSTR

To extract the substring from any String we use SUBSTR
for e.g., mythass → thass

SUBSTR (STRING, START, LENGTH);

select SUBSTR('mythass', 3, 5) from dual;

------------------------
output: thass


INSTR

To get the position of any string we use INSTR
for e.g., mythass → thass


INSTR (STRING, SUBSTR, BEGIN_with_Nth_character_for_SUBSTR, RETRN_POS_Nth_OCCURENCE_OF_SUBSTR);
 /* N IS NUMBER */

SELECT INSTR('mythass','thass', 1, 1) FROM DUAL;
------------------------

output: 3

Some other examples-

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 1, 1) FROM DUAL;

-- starting from the 1st character of substring, for 1st occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 1, 2) FROM DUAL;

-- starting from the 1st character of substring, for 2nd occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 2, 2) FROM DUAL;         

-- starting from the 2nd character of substring, for 2nd occurence

-- same output as above query because it is getting the same position of OR

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 3, 2) FROM DUAL;

-- starting from the 3rd character of substring, for 2nd occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 3, 3) FROM DUAL;

-- starting from the 3rd character of substring, for 3rd occurence