Sunday, 26 November 2017

How to select a substring string between two special characters in a String using Oracle sql

SELECT SUBSTRING('abc(praveen)def()tutorial',INSTR('abc(praveen)def()tutorial','(',-1,1)-1,INSTR('abc(praveen)def()tutorial',')',-1,1)-1-INSTR('abc(praveen)def()tutorial','(',-1,1)-1) from DUAL;

The above query output is null

SELECT SUBSTRING('abc(praveen)def()tutorial',INSTR('abc(praveen)def()tutorial','(',-1,2)-1,INSTR('abc(praveen)def()tutorial',')',-1,2)-1-INSTR('abc(praveen)def()tutorial','(',-1,2)-1) from DUAL;

 The above query output is praveen