4 Replies Latest reply: Apr 3, 2013 11:52 PM by jeneesh RSS

    Removing '/' from a feld value to create two rows

    MaheshGx
      i have a table having structure as

      CREATE TABLE TEST
      (
      MOLDCODE VARCHAR2(20 BYTE) NOT NULL ENABLE,
      PRODCODE VARCHAR2(255 BYTE),
      DIAMCONTAINER NUMBER(4,1),
      SEGMENT NUMBER(2,0)
      )

      And it has data as

      MOLDCODE           PRODCODE           DIAMCONTAINER      SEGMENT

      ADE16920-1     CJ02A/SD51A     96          10
      ADE20206      CT25A          95          8
      ADE16912-1     DG31A          57          10
      ADE16913     AS30A/DE28A      96          10
      ADE17845     CJ24A/SD36A     96          10


      i want to create another table having which will remove / from prodcode and it will separate both values and form two rows having same values for other fields.
      for example.

      In Source  :-
      MOLDCODE      PRODCODE      DIAMCONTAINER      SEGMENT

      ADE16920-1     CJ02A/SD51A     96          10
      ADE16913     AS30A/DE28A     96          10

      In Target:-
      MOLDCODE      PRODCODE      DIAMCONTAINER      SEGMENT

      ADE16920-1     CJ02A          96          10
      ADE16920-1     SD51A          96          10
      ADE16913     AS30A          96          10
      ADE16913      DE28A          96          10

      Can it be achieved ?
      Need help for the same.
        • 1. Re: Removing '/' from a feld value to create two rows
          jeneesh
          If you will have at max two values only for prodcode, you can do as
          create table test2 as
          select      MOLDCODE,
               regexp_substr(PRODCODE,'[^/]+',1,rn) prodcode,
               DIAMCONTAINER,SEGMENT
          from test t,
               (
                 select 1 rn from dual union all
                 select 2 rn from dual 
               )
          where regexp_substr(PRODCODE,'[^/]+',1,rn) is not null
          or rn = 1;
          Edited by: jeneesh on Apr 4, 2013 10:07 AM
          RN condition added
          • 2. Re: Removing '/' from a feld value to create two rows
            Manik
            Check this:
                SELECT moldcode,
                       REGEXP_SUBSTR (procode,
                                      '[^/]+',
                                      1,
                                      LEVEL)
                          procode,
                       diamcontainer,
                       segment
                  FROM t
            CONNECT BY     LEVEL <= REGEXP_COUNT (procode, '/') + 1
                       AND moldcode = PRIOR moldcode
                       AND PRIOR SYS_GUID () IS NOT NULL;
            Cheers,
            Manik.
            • 3. Re: Removing '/' from a feld value to create two rows
              nkvkashyap
              create table testing1 as
              select 'ADE16920-1' MOLDCODE, 'CJ02A/SD51A' PRODCODE, 96 DIAMCONTAINER,10 SEGMENT from dual
              union
              select 'ADE16913', 'AS30A/DE28A', 96 ,10 from dual
              union
              select 'ADE20206', 'CT25A', 95, 8 from dual
              union
              select 'ADE16912-1','DG31A', 57, 10 from dual
              union
              select 'ADE17845', 'CJ24A/SD36A', 96, 10 from dual

              ;

              select * from
              (
              select MOLDCODE,substr(PRODCODE,1,instr(PRODCODE,'/')-1) PRODCODE,DIAMCONTAINER,SEGMENT from testing1 union select MOLDCODE,substr(PRODCODE,instr(PRODCODE,'/')+1) PRODCODE,DIAMCONTAINER,SEGMENT from testing1
              ) where PRODCODE is not null;

              Regards,
              Kashyap Varma N
              • 4. Re: Removing '/' from a feld value to create two rows
                jeneesh
                nkvkashyap wrote:
                create table testing1 as
                select 'ADE16920-1' MOLDCODE, 'CJ02A/SD51A' PRODCODE, 96 DIAMCONTAINER,10 SEGMENT from dual
                union
                select 'ADE16913', 'AS30A/DE28A', 96 ,10 from dual
                union
                select 'ADE20206', 'CT25A', 95, 8 from dual
                union
                select 'ADE16912-1','DG31A', 57, 10 from dual
                union
                select 'ADE17845', 'CJ24A/SD36A', 96, 10 from dual

                ;

                select * from
                (
                select MOLDCODE,substr(PRODCODE,1,instr(PRODCODE,'/')-1) PRODCODE,DIAMCONTAINER,SEGMENT from testing1 union select MOLDCODE,substr(PRODCODE,instr(PRODCODE,'/')+1) PRODCODE,DIAMCONTAINER,SEGMENT from testing1
                ) where PRODCODE is not null;

                Regards,
                Kashyap Varma N
                You will have to take care of NULL if the second Product Code is not present.You could add a filter in the second SELECT in the Union to do that.

                And probably UNION can be replaced with UNION ALL.

                And "substr(PRODCODE,1,instr(PRODCODE,'/')-1) " (and other SUBSTR also) will fail if there is no "/" present in the product code (which is there in the sample data)