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