SUBSTR, Instr in oracle

The Instr function is used to find the position of a string in another string. Optionally you can provide position m to start searching for the string and the occurrence n of the string. By default m and n are 1 which means to start the search at the beginning of the search and the first occurrence.

Syntax: instr('Main String', 'substring', [m], [n])

SELECT instr('oralce apps','app') FROM DUAL;

SELECT instr('oralce apps is a great application','app',1,2) FROM DUAL;

. SUBSTR

The Substr function returns specified characters from character value starting at position m and n characters long. If you omit n, all characters starting from position m to the end are returned.

Syntax: substr(string [,m,n])

SELECT substr('ORACLE DATA RECOVERY',8,4) FROM DUAL;

SELECT substr('ORACLE DATA PUMP',8) FROM DUAL;

.DEPARTMENT_ID = D.DEPARTMENT_ID)