Question discussion

The SQL statementSELECT SUBSTR('abcdefghij', INSTR('123321234', '2', 3, 2), 2) FROM DUAL;prints

297107Medium 1 reply 1,084 views

Discussion 1

0
CCommunity
gh

Another form of INSTR function used in ORACLE is:
INSTR (str, pattern, [starting position, [nth location]]): Finds the starting location of the nth occurrence of pattern beginning in the starting position-th position in string str.
Example: - SELECT INSTR('kolkata', 'a', 1, 2) FROM DUAL;
will output 7 as the starting location of 2nd occurrence of pattern 'a' from starting position 1 in string 'kolkata' is 7.

In the above query INSTR('123321234', '2', 3, 2) will give the output 7 as the starting location of 2nd occurrence of pattern '2' from starting location three in string '123321234' is 7.

Now SUBSTR function becomes SUBSTR('abcdefghij