asked on 12/27/2019

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

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.

Oracle Database

Last Comment

srikotesh

12/30/2019

Ryan Chong

12/27/2019

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

ASKER CERTIFIED SOLUTION

johnsone

12/27/2019

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Mark Geerlings

12/27/2019

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.

srikotesh

12/30/2019

ASKER

thanks