Extract characters between specific strings in Oracle

To Extract specific characters from text we usecombination of  SUBSTR or INST functions .



SUBSTR Function :

It cuts a part of the Character from a a position we  define.



For example :

SELECT   SUBSTR ('HeIsGood', 3) FROM DUAL

The result is :

IsGood



INSTR Function :

It calculates the position of specific text we define inside Character.

For example :

SELECT   INSTR ('cocacola', 'co', 2) FROM DUAL

The result is :

5


We can use combination of INSTR and SUBSTR to extract characters between specific text as following:


SELECT SUBSTR('1/9/1438', 1 ,INSTR('1/9/1438', '/', 1, 1)-1) "DAY", SUBSTR('1/9/1438', INSTR('1/9/1438','/', 1, 1)+1,

INSTR('1/9/1438','/',1,2)-INSTR('1/9/1438','/',1,1)-1) "MONTH", SUBSTR('1/9/1438', INSTR('1/9/1438','/', 1, 2)+1) "YEAR"

FROM dual;

The result is :
1  9  1438

Thanks to PSOUG.org