This discussion is archived
4 Replies Latest reply: Apr 3, 2013 9:52 PM by jeneesh RSS

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

MaheshGx Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points