Split in oracle

Question

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

Yes. It would be a train wreck, which is why I didn't post an answer. Can you do it in the application layer? — Bohemian, Apr 05 '17 at 13:28
What have you tried untill now? How many separators can be in one source column (4 as your example?)? — etsa, Apr 05 '17 at 13:28
no access on application layer, there can be max 5 seperators — Maddy, Apr 05 '17 at 13:36
Test carefully! Add some NULL list elements and always expect the unexpected! — Gary_W, Apr 05 '17 at 14:06

score 3 · Answer 1 · answered Apr 05 '17 at 13:53

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 .",".

Didn't work on 11g: ERROR at line 2: ORA-19107: invalid XQueryX - unsupported construct - fn:tokenize(). Does it require a certain package to be installed? — Gary_W, Apr 05 '17 at 14:05
No, my solution is using standard mechanism. You probably have oracle version < 11.2.0.4. try to replace `tokenize` with `ora:tokenize`. These two method work almost in the same way. — Arkadiusz Łukasiewicz, Apr 05 '17 at 14:46

score 2 · Answer 2 · edited May 23 '17 at 11:54

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)!

score 1 · Accepted Answer · answered Apr 05 '17 at 13:44

1

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;

answered Apr 05 '17 at 13:44

Oto Shavadze

40,603
55
152
236

Please test with NULL list elements. You may be surprised at what you find. – Gary_W Apr 05 '17 at 13:59
@Gary_W - what you mean by `with NULL list element`? when there is nothing between separators? – Oto Shavadze Apr 05 '17 at 14:06
Yes. See here for something I found and fixed once: http://stackoverflow.com/a/31464699/2543416 – Gary_W Apr 05 '17 at 14:08

Split in oracle

3 Answers3