CODEWITHSUNDEEP
oracle-databasesplitsubstr
user3845185
user3845185

Reputation: 125

Split column in Oracle

I have a column in an Oracle Database which has something like this data

column1
/opt/log/data/abcd.efghi.jklmn.aaa.txt
/opt/log/data/abbbcd.efccghi.jkdsdflmn.abab.txt
/opt/log/data/nmvcnmcd.efjhjghi.jkvslmn.abcbc.txt
/opt/log/data/hjsdhj.hjfdhdf.hdfhjd.aghag.txt
/opt/log/data/dfhjfdhj.yureyer.qwtyq.hjahjh.txt

I want to split the data in such a way that

**firstdot    seconddot      thirdnfourthdot**
abcd        efghi          jklmn.aaa
abbbcd      efccghi        jkdsdflmn.abab
nmvcnmcd    efjhjghi       jkvslmn.abcbc
hjsdhj      hjfdhdf        hdfhjd.aghag
dfhjfdhj    yureyer        qwtyq.hjahjh

I can get the seconddot value by 

select substr(column1,instr(column1,'.',1+1,instr(column1,'.',1,2)-instr(column1,'.',1,1)-1) as secondot

but I could not get the rest. Can you guys help.

Thanks a lot

Upvotes: 0

Views: 57

Answers (2)

Aleksej
Aleksej

Reputation: 22949

Without regexp, you need to reply the same logic for every substring you need, every timi picking the initial position and the leght, based on the position of the "terminator" of that substring.

/* input data */
    with yourTable(column1) as (
        select '/opt/log/data/abcd.efghi.jklmn.aaa.txt' from dual union all
        select '/opt/log/data/abbbcd.efccghi.jkdsdflmn.abab.txt' from dual union all
        select '/opt/log/data/nmvcnmcd.efjhjghi.jkvslmn.abcbc.txt' from dual union all
        select '/opt/log/data/hjsdhj.hjfdhdf.hdfhjd.aghag.txt' from dual union all
        select '/opt/log/data/dfhjfdhj.yureyer.qwtyq.hjahjh.txt' from dual
    )
/* query */
    select substr(column1, instr(column1, '/', -1) +1, instr(column1, '.') - instr(column1, '/', -1)-1) firstDot,
           substr(column1, instr(column1, '.') +1, instr(column1, '.', 1, 2) - instr(column1, '.') -1) secondDot,
           substr(column1, instr(column1, '.', 1, 2) +1, instr(column1, '.', 1, 4) - instr(column1, '.', 1, 2) -1) thirdAndFourthDot
    from yourTable

gives:

FIRSTDOT        SECONDDOT       THIRDANDFOURTHD
--------------- --------------- ---------------
abcd            efghi           jklmn.aaa
abbbcd          efccghi         jkdsdflmn.abab
nmvcnmcd        efjhjghi        jkvslmn.abcbc
hjsdhj          hjfdhdf         hdfhjd.aghag
dfhjfdhj        yureyer         qwtyq.hjahjh

In a more readable way:

select substr(column1, lastSlashPos +1, firstDotPos - lastSlashPos -1) as firstDot,
       substr(column1, firstDotPos +1, secondDotPos - firstDotPos -1) as secondDot,
       substr(column1, secondDotPos +1, fourthDotPos - secondDotPos -1) as thirdAndFourthDot
from (
        select instr(column1, '/', -1) as lastSlashPos,
               instr(column1, '.') as firstDotPos,
               instr(column1, '.', 1, 2) as secondDotPos,
               instr(column1, '.', 1, 3) as thirdDotPos,
               instr(column1, '.', 1, 4) as fourthDotPos,
               column1
        from yourTable
     )

Upvotes: 1

Kacper
Kacper

Reputation: 4818

select substr('/opt/log/data/abcd.efghi.jklmn.aaa.txt',instr('/opt/log/data/abcd.efghi.jklmn.aaa.txt','/',-1) + 1) from dual;

This will give you text after last /

Then you need to apply instr for .:

select 
  substr(text, 1,  instr(text,'.', 1) - 1), 
  substr(text, instr(text,'.', 1) + 1, instr(text,'.', 2) - 1), 
  substr(text, instr(text,'.', 2) + 1) 
from (
    select substr('/opt/log/data/abcd.efghi.jklmn.aaa.txt',instr('/opt/log/data/abcd.efghi.jklmn.aaa.txt','/',-1) + 1) text from dual
);

Upvotes: 0

Related Questions