-
string processing help
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:
PHP Code:
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;
-
Code:
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
-
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.
-
Code:
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:
Code:
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.
-
