how to get the substring of a column(String splitted with _ symbol) using oracle

srikotesh used Ask the Experts™
Hi Experts,

Table name -->Test
column name -->filename
below is the file name value always it will split with 3 underscore symbols(_)
i have get the substring 2nd  underscore value-->08112017085147
values of the file name will be dynamic.

can some suggest how do i fetch the substring.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

try use REGEXP_SUBSTR function.


to implement:

with cte as
    Select 'EE_is_great_resource' yourField from dual union all
    Select 'Bla bla..' yourField from dual union all
    Select 'PTXYZPT_XXX00170_08112017085147_80462' yourField from dual
REGEXP_SUBSTR (yourField, '[^_]+', 2, 3)
from cte

Open in new window

Senior Oracle DBA
This does it without the expensive regular expression calls.
SELECT Substr(filename, Instr(filename, '_', 1, 2) + 1, 
              Instr(filename, '_', 1, 3) - Instr(filename, '_', 1, 2) - 1) 
FROM   test 

Open in new window

That will give you the expected result you posted.  However, the wording of your requirement seems to be different.
Mark GeerlingsDatabase Administrator

I agree that using a combination of SUBSTR and INSTR is the most-efficient way to solve this problem.  The third parameter for INSTR (the "2" in this example): Instr(filename, '_', 1, 2) tells Oracle which occurrence of the "_" character to use.  And the "+ 1" or "- 1" along with INSTR are to adjust those values so the SUBSTR command starts or ends where you need it to.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial