string processing help

[Sonia]

I am trying to write a procedure to Cut the string and insert the values into a table. But not able to really succeed.

Can somebody help me in improvising this code.
ServerString is like

data1;data2;data3,data01;;data03
(each row separated by , and column data separated by ; )



This string should go into table as:

col1       col2         col3
-------------------------------
data1     data2       data3
data01                  data03 

Create or replace procedure ProcessString (ServerString IN Varchar2)
Is
v_serverId Server_Detail.Server_Id%type;

Cursor get_vals is Select
regexp_substr(astring,'[^;]+',1,1) v1,
regexp_substr(astring,'[^;]+',1,2) v2,
regexp_substr(astring,'[^;]+',1,3) v3,
regexp_substr(astring,'[^;]+',1,4) v4,
regexp_substr(astring,'[^;]+',1,5) v5,
regexp_substr(astring,'[^;]+',1,6) v6
from (select ServerString astring from dual);




Begin

for a in get_vals loop
Begin

dbms_output.put_line('1 value:'||a.v1);
dbms_output.put_line('2 value:'||a.v2);
dbms_output.put_line('3 value:'||a.v3);
dbms_output.put_line('4 value:'||a.v4);
dbms_output.put_line('5 value:'||a.v5);

End;
end loop;

End;

[ebrian]

with t as (select regexp_substr(a1, '[^,]+', 1, level) str
           from t2
           connect by regexp_substr(a1, '[^,]+', 1, level) is not null)
select regexp_substr(str, '[^;]*') col1
,      trim(';' from regexp_substr(str, ';[^;]*;')) col2
,      regexp_substr(str, '[^;]*$') col3
from t

[Sonia]

Excellent ebrain.
Thx.

Just wondering if this can be modified for variable columns.

Like i have string length varying.


For eg.,
i have strings of columns 2,3 and 4

eg
data1;data2;data3,data01;;data03
data1;data2,dataA;dataB

If some data is missing then it will be blank. like.,
data1;;data2
But each string will have fixed number of columns.
ie.,
3;3;3,3;3;3
2;2,2;2

Instead of writing 3 different queries, can i modify the existing one.

[ebrian]

SQL> select * from t2;

A1
--------------------------------------------------------
data1;data2,dataA;dataB
;bbb,aaa;
abc1;abc2;,zzz1;;zzz3,;;yyy3,;mmm2;,data01;data02;data03

SQL> with t as (select distinct rn, regexp_substr(a1, '[^,]+', 1, level) str
  2             from (select rownum rn, t2.*
  3                   from t2)
  4             connect by regexp_substr(a1, '[^,]+', 1, level) is not null
  5            )
  6  select regexp_substr(str, '[^;]*') col1
  7  ,      translate(regexp_substr(str, ';[^;,]*(;|,|$)'),'1;,','1') col2
  8  ,      trim(regexp_substr(replace(str,';',' ; '), '[^;]+', 1, 3)) col3
  9  from t
 10  order by rn;

COL1       COL2       COL3
---------- ---------- ----------
data1      data2
dataA      dataB
           bbb
aaa
abc1       abc2
zzz1                  zzz3
           mmm2
                      yyy3
data01     data02     data03

If you are only going to manipulate one string at a time, then the following will suffice:

SQL> select * from t2;

A1
------------------------------------------------------------------------------
abc1;abc2;,zzz1;;zzz3,;;yyy3,;mmm2;,data01;data02;data03

SQL> with t as (select regexp_substr(a1, '[^,]+', 1, level) str
  2             from t2
  3             connect by regexp_substr(a1, '[^,]+', 1, level) is not null
  4            )
  5  select regexp_substr(str, '[^;]*') col1
  6  ,      translate(regexp_substr(str, ';[^;,]*(;|,|$)'),'1;,','1') col2
  7  ,      trim(regexp_substr(replace(str,';',' ; '), '[^;]+', 1, 3)) col3
  8  from t;

COL1       COL2       COL3
---------- ---------- ----------
abc1       abc2
zzz1                  zzz3
                      yyy3
           mmm2
data01     data02     data03

If you are doing a huge number of records, then PL/SQL may be more performant.

[Sonia]

excellent.
Thx ebrain.