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

[daphnny]

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.

[GynJer]

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.

[oratune]

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.

[daphnny]

Thank you David and GynJer. I tested David's script and it works beautifully.