Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    PA
    Posts
    9

    Exclamation Unanswered: Splitting Data in a row - desparately need help!

    Hello,

    I have to 'normalize' data that currently contains single fields with data values delimited by comma.
    For instance MyColumn = Mike, Jim, Stan.

    Fields may have one value, or many.

    Need to parse the fields and extract each piece of data into a new row.

    Anyone have a PL/SQL script?

    Thanks so much in advance!

    mzimm

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Splitting Data in a row - desparately need help!

    Originally posted by mzimm
    Hello,

    I have to 'normalize' data that currently contains single fields with data values delimited by comma.
    For instance MyColumn = Mike, Jim, Stan.

    Fields may have one value, or many.

    Need to parse the fields and extract each piece of data into a new row.

    Anyone have a PL/SQL script?

    Thanks so much in advance!

    mzimm
    create table junk(id number, text varchar2(100) );
    create table code( id number, code varchar2(100) );
    insert into junk values (1,'Mike, Jim, Stan');
    insert into junk values (2, 'Fred, Bill' );

    DECLARE
    v_text VARCHAR2(100);
    v_code VARCHAR2(100);
    BEGIN
    FOR r IN ( SELECT id, text FROM junk )
    LOOP
    v_text := r.text;
    WHILE INSTR( v_text, ', ' ) > 0
    LOOP
    -- Get value up to next comma
    v_code := SUBSTR( v_text, 1, INSTR( v_text, ', ' )-1 );
    INSERT INTO code (id, code) VALUES ( r.id, v_code );
    -- Remove value from list
    v_text := SUBSTR( v_text, INSTR( v_text, ', ' )+2 );
    END LOOP;
    -- Last code in string
    INSERT INTO code (id, code) VALUES ( r.id, v_text );
    END LOOP;
    END;
    /

    SELECT * FROM code;

    ID CODE
    ---------- ----------
    1 Mike
    1 Jim
    1 Stan
    2 Fred
    2 Bill

  3. #3
    Join Date
    Oct 2002
    Location
    PA
    Posts
    9

    Thumbs up Re: Splitting Data in a row - desparately need help!

    I can't thank you enough for the time you saved me!
    Works great.

    Thanks again!

    mzimm



    ----------------------------------------



    Originally posted by andrewst
    create table junk(id number, text varchar2(100) );
    create table code( id number, code varchar2(100) );
    insert into junk values (1,'Mike, Jim, Stan');
    insert into junk values (2, 'Fred, Bill' );

    DECLARE
    v_text VARCHAR2(100);
    v_code VARCHAR2(100);
    BEGIN
    FOR r IN ( SELECT id, text FROM junk )
    LOOP
    v_text := r.text;
    WHILE INSTR( v_text, ', ' ) > 0
    LOOP
    -- Get value up to next comma
    v_code := SUBSTR( v_text, 1, INSTR( v_text, ', ' )-1 );
    INSERT INTO code (id, code) VALUES ( r.id, v_code );
    -- Remove value from list
    v_text := SUBSTR( v_text, INSTR( v_text, ', ' )+2 );
    END LOOP;
    -- Last code in string
    INSERT INTO code (id, code) VALUES ( r.id, v_text );
    END LOOP;
    END;
    /

    SELECT * FROM code;

    ID CODE
    ---------- ----------
    1 Mike
    1 Jim
    1 Stan
    2 Fred
    2 Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •