1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How can I split a multi-delimited field into several rows, and add new fields?

Discussion in 'SQL PL/SQL' started by darkgilead, Mar 7, 2013.

  1. darkgilead

    darkgilead Guest

    Hi everyone! I am new to Oracle, and usually write queries for t-SQL. I have a query that produces around 11 fields, and one of which is a multi-delimited field and the other 10 are dimension fields. I would like to split that field into several rows, and have the other 10 fields just repeated for each one. Here is an example of the data in the 11th field:

    Column 11
    34^56^78,59

    There are two delimiters in the field, a carat and a comma. This field is used to reference document numbers that are needed to be sent in. The carat represents the word "Or" and the comma represents the word "And". I would like to have the output of each field to be a repeat of the 10 dimension fields, plus 3 new fields. The first new field would be the document number, the second new field would be the position within the original delimited field(1, 2, 3, etc.) , and the last field would be one of three logic words :First (if it is the first value), Or (if the value followed a carat), And (If the value followed a comma). Example of the output from the above value would be (please ignore the underlines, i just did that to maintain spacing):

    Column 11 Column 12 Column 13
    34_______ 1_______ First
    56_______ 2_______ Or
    78_______ 3_______ Or
    59_______ 4_______ And

    Any thoughts on this? I have found a few solutions online on how to break up the delimited field into rows, but never with multiple delimiters or with extra logic for the added fields. Any help would be SUPER appreciated! ^_^

    Thanks in advance!
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is slightly difficult to understand, but still achievable. try the following code.

    Code (Text):
    SQL> select * from xtest;

    X            Y XSTR
    --- ---------- --------------------
    abc        110 34^56^78,59
    def        111 12^22^45,99
    lmn        113 15,21^10,89
    xyz        112 23,41,16^49

    SQL> with rec1  as ( select x, y, xstr ,
      2              TRANSLATE(xstr,'X0123456789','X') xlmt FROM XTEST  ) ,
      3  rec2 as ( select level xlvl from dual connect by level <=4) ,
      4  rec3 as ( select x,y , xstr  , SUBSTR(xlmt,xlvl,1) xlmt , xlvl
      5              from rec1  , rec2 ),
      6  rec4 as ( select x,y, xstr,xlmt,xlvl,
      7            RANK() over (partition by xstr,xlmt order by xlvl ) xpos ,
      8            LAG (xlmt,1)   over (partition by xstr order by xlvl ) lprev
      9            from rec3   ) ,
     10  rec5 as ( select  x,y, xstr,xlmt,xlvl, xpos,lprev ,
     11            LAG (xpos,1) over (partition by xstr order by xlvl ) pprev
     12            from rec4 )
     13  select  x,y,
     14          DECODE( xlvl,1, SUBSTR(xstr,1,INSTR(xstr,xlmt,1,1)-1)  ,
     15          2, SUBSTR(xstr,INSTR(xstr,lprev,1,pprev)+1 ,
     16                INSTR(xstr,xlmt,1,xpos) -INSTR(xstr,lprev,1,pprev)-1 ),
     17          3, SUBSTR(xstr,INSTR(xstr,lprev,1,pprev)+1 ,
     18                 INSTR(xstr,xlmt,1,xpos) -INSTR(xstr,lprev,1,pprev)-1 ),
     19          4, SUBSTR(xstr,INSTR(xstr,lprev,1,pprev)+1 ))  xstr ,
     20          xlvl ,
     21          DECODE(LPREV,NULL,'First','^','Or',',','And')

     22  from rec5 ;

    X            Y XSTR                       XLVL DECOD
    --- ---------- -------------------- ---------- -----
    def        111 12                            1 First
    def        111 22                            2 Or
    def        111 45                            3 Or
    def        111 99                            4 And
    lmn        113 15                            1 First
    lmn        113 21                            2 And
    lmn        113 10                            3 Or
    lmn        113 89                            4 And
    xyz        112 23                            1 First
    xyz        112 41                            2 And
    xyz        112 16                            3 And
    xyz        112 49                            4 Or
    abc        110 34                            1 First
    abc        110 56                            2 Or
    abc        110 78                            3 Or
    abc        110 59                            4 And

    16 rows selected.

    SQL>
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Nicely done.