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

srikotesh
srikotesh used Ask the Experts™
on
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.
PTXYZPT_XXX00170_08112017085147_80462

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

Do more with

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

Commented:
try use REGEXP_SUBSTR function.

REGEXP_SUBSTR
https://www.techonthenet.com/oracle/functions/regexp_substr.php

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
)
Select
REGEXP_SUBSTR (yourField, '[^_]+', 2, 3)
from cte

Open in new window

Senior Oracle DBA
Commented:
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

Commented:
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.

Author

Commented:
thanks

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