DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to break down value in one column into several columns?

  1. #1
    Join Date
    Dec 2000
    Posts
    37

    Cool

    Please someone help me on this one.

    I have a table like this:

    SQL> desc cs_accounting_log;
    Name Null? Type
    ------------------------------ ---- -------- --------------------
    LOG_ID NOT NULL NUMBER(38)
    BLOB_ORDINAL NOT NULL NUMBER(38)
    BLOB_DATA NOT NULL VARCHAR(255)

    And in column BLOB_DATA, one record is made of mutiple values. Ex.

    SQL> select * from cs_accounting_log where BLOB_DATA like '%apurcell%';

    LOG_ID BLOB_ORDINAL
    ---------- ------------
    BLOB_DATA
    --------------------------------------------------------------------------------
    80014 1
    ubr117.cmts.hcvlny.cv.net apurcell tty2 167.206.112.181 start server=auth0 time=05:45:46 date=10/21/2001 task_id=2031 start_time=1003657546 timezone=edt service=shell

    80015 1
    ubr117.cmts.hcvlny.cv.net apurcell tty2 167.206.112.181 stop server=auth0 time=05:45:48 date=10/21/2001 task_id=2031 start_time=1003657546 timezone=edt service=shell
    disc-cause=1 disc-cause-ext=1020 elapsed_time=2 nas-rx-speed=0 nas-tx-spee
    d=0

    80016 1
    ubr117.cmts.hcvlny.cv.net apurcell tty2 167.206.112.181 start server=auth0 time=05:46:18 date=10/21/2001 task_id=2032 start_time=1003657577 timezone=edt service=shell


    How do I break down those fields in BLOB_DATA column so that I can do some query? I am thinking of creating another table with multiple columns, but how do I load the data in that table?

    Any help will be greatly appreciated.




  2. #2
    Join Date
    Nov 2001
    Posts
    13
    A thought:

    It appears that the column you have there is delimited by spaces. You could write a script (possibly perl) that would create a SQL script that would insert each of the parts into another table.

    Just a thought.


  3. #3
    Join Date
    Nov 2001
    Location
    Central U.S.
    Posts
    35
    Since the data string has embedded spaces that separate values you could use the INSTR() function from within a PL/SQL block to 'parse' the string into separate fields. As an example:

    declare
    pos number:=0;
    next_pos number:=0;
    len number:=0;
    var1 varchar2(40);
    var2 varchar2(40);
    var3 varchar2(40);
    var4 varchar2(40);
    cursor get_string is
    select string
    from string_table;
    begin
    for strng in get_string loop
    next_pos := instr(strng.string, ' ', pos+1, 1);
    len := next_pos - pos - 1;
    var1 := substr(strng.string, pos + 1, len);
    pos := next_pos;
    next_pos := instr(strng.string, ' ', pos+1, 1);
    len := next_pos - pos - 1;
    var2 := substr(strng.string, pos + 1, len);
    pos := next_pos;
    next_pos := instr(strng.string, ' ', pos+1, 1);
    len := next_pos - pos - 1;
    var3 := substr(strng.string, pos + 1, len);
    pos := next_pos;
    next_pos := instr(strng.string, ' ', pos+1, 1);
    if next_pos = 0 then
    var4 := substr(strng.string, pos+1);
    else
    len := next_pos - pos - 1;
    var4 := substr(strng.string, pos + 1, len);
    end if;
    insert into var_table
    values
    (var1, var2, var3, var4);
    commit;
    pos := 0;
    end loop;
    end;
    /

    Hopefully this will give you some idea of how to proceed.

    David D. Fitzjarrell
    Oracle Certified DBA

  4. #4
    Join Date
    Dec 2000
    Posts
    37
    Thank you David and GynJer. I tested David's script and it works beautifully.

Posting Permissions

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


Click Here to Expand Forum to Full Width