CODEWITHSUNDEEP
oracle-databaseplsql
Maddy
Maddy

Reputation: 3816

Split in oracle

I have following strings:

with strlist as ( 
select '#RH1#RH1-GEN#RH1-GEN-RW1' col from dual
union all
select  '#RH1#RH1-GEN#RH1-GEN-RW1#RH1-GEN-RW1-RRWA' col  from dual
union all
select  '#RH1' col  from dual
)
select * from strlist

Desired out put is 

COL1     COL2       COL3            COL4
RH1     RH1-GEN    RH1-GEN-RW1      NULL
RH1     RH1-GEN    RH1-GEN-RW1      RH1-GEN-RW1-RRWA
RH1     NULL      NULL              NULL

Is there an easy way for this?

I tried below.. with few exceptions its working. But am looking for a better/accurate alternative:

with strlist as ( 
select '#RH1#RH1-GEN#RH1-GEN-RW1' FULLPATH from dual
union all
select  '#RH1#RH1-GEN#RH1-GEN-RW1#RH1-GEN-RW1-RRWA' FULLPATH  from dual
union all
select  '#RH1' FULLPATH  from dual
)
select FULLPATH, 
            SUBSTR (FULLPATH,  1, REGEXP_INSTR(FULLPATH, '(#)+', 1, 2))   PHASE,
             SUBSTR (FULLPATH,  REGEXP_INSTR(FULLPATH, '(#)+', 1, 2), REGEXP_INSTR(FULLPATH, '(#)+', 1, 3) - REGEXP_INSTR(FULLPATH, '(#)+', 1, 2))   AREA,
            SUBSTR (FULLPATH,  REGEXP_INSTR(FULLPATH, '(#)+', 1, 3), REGEXP_INSTR(FULLPATH, '(#)+', 1, 4) - REGEXP_INSTR(FULLPATH, '(#)+', 1, 3))   AREA,
            SUBSTR (FULLPATH,  REGEXP_INSTR(FULLPATH, '(#)+', 1, 4), REGEXP_INSTR(FULLPATH, '(#)+', 1, 5) - REGEXP_INSTR(FULLPATH, '(#)+', 1, 4))   SYSTEM,
            SUBSTR (FULLPATH,  -REGEXP_INSTR(FULLPATH, '(#)+', 1, 5))   TAG
 from strlist

Upvotes: 0

Views: 72

Answers (3)

Gary_W
Gary_W

Reputation: 10360

This version allows for NULL list elements as shown in the last string. 

SQL> with strlist(col) as (
   select '#RH1##RH1-GEN-RW1' from dual union
   select '#RH1#RH1-GEN#RH1-GEN-RW1#RH1-GEN-RW1-RRWA' from dual union
   select '#RH1' from dual union
   select '#RH1##RH1-GEN-RW1#RH1-GEN-RW1-RRWA' from dual
   )
   select
     regexp_substr(col, '(.*?)(#|$)', 1, 2, NULL, 1) col1,
     regexp_substr(col, '(.*?)(#|$)', 1, 3, NULL, 1) col2,
     regexp_substr(col, '(.*?)(#|$)', 1, 4, NULL, 1) col3,
     regexp_substr(col, '(.*?)(#|$)', 1, 5, NULL, 1) col4
   from strlist;

COL1       COL2       COL3                 COL4
---------- ---------- -------------------- --------------------
RH1
RH1                   RH1-GEN-RW1
RH1                   RH1-GEN-RW1          RH1-GEN-RW1-RRWA
RH1        RH1-GEN    RH1-GEN-RW1          RH1-GEN-RW1-RRWA

SQL>

NOTE! Regular expressions of the format '[^#]+' often used to split strings DO NOT HANDLE NULL ELEMENTS! See here for more info: https://stackoverflow.com/a/31464699/2543416. Get out of the habit of using that format lest your queries one day start returning the wrong data (I actually found this situation so now I'm on a mission)!

Upvotes: 2

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

with strlist
     as (select '#RH1#RH1#GEN#RH1-GEN-RW1' FULLPATH from dual
         union all
         select '#RH1#RH1-GEN#RH1-GEN-RW1#RH1-GEN-RW1-RRWA' FULLPATH from dual
         union all
         select '#RH1' FULLPATH from dual)
select *
  from strlist
     , xmltable('let $i := 0 return <list> {for $i in tokenize($in,",") return <col>{$i}</col>} </list>'
                passing replace(FULLPATH, '#', ',') as "in"
                columns col1 varchar2(100) path '//list/col[2]/text()'
                      , col2 varchar2(100) path '//list/col[3]/text()'
                      , col3 varchar2(100) path '//list/col[4]/text()'
                      , col4 varchar2(100) path '//list/col[5]/text()');

If you need more columns just add next one. There is a small trick in code. Because for xuqery char "#" is special. I have to replace it with somthing else .",".

Upvotes: 3

Oto Shavadze
Oto Shavadze

Reputation: 42753

If Maximum 5 separators, then try this:

    with strlist as ( 
    select '#RH1#RH1-GEN#RH1-GEN-RW1' col from dual
    union all
    select  '#RH1#RH1-GEN#RH1-GEN-RW1#RH1-GEN-RW1-RRWA' col  from dual
    union all
    select  '#RH1' col  from dual
    )
    SELECT 
        REGEXP_SUBSTR(col, '[^#]+', 1, 1) col1,
        REGEXP_SUBSTR(col, '[^#]+', 1, 2) col2,
        REGEXP_SUBSTR(col, '[^#]+', 1, 3) col3,
        REGEXP_SUBSTR(col, '[^#]+', 1, 4) col4,
        REGEXP_SUBSTR(col, '[^#]+', 1, 5) col5
    FROM  strlist;

Upvotes: 1

Related Questions