Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert comma delimed values to distinct records

792290Nov 8 2011 — edited Nov 10 2011
Hello,
i wrote the following code to get the data from comma delimited row into distinct values to another table. say for example my data into column looks like this.
long_string column data looks like this:
11,12,14,16,13,18,17
11,12,13,15,16,17,18,19,20,30
11,20,30,40,67,90,87,65,76,43,23,56,78,90,12,22,34 - which doesn't have a comma at the end of the data.

i need to convert this into distinct record for each rows data.
i used mod function...
can some one help me.

DECLARE
cursor c_1 is select LONG_STRING from TEST;
rec c_1%rowtype;
temp_VAR varchar2(100);
size_of_VAR number;
number_of_VAR number;
i number;
cur_VAR varchar2(3);
begin
open c_1;
loop
fetch c_1 into rec;
exit when c_1%notfound;
temp_VAR := rec.LONG_STRING;
size_of_VAR := length(temp_VAR);
number_of_VAR := size_of_VAR/4;
if (mod (size_of_VAR,4) = 0) then
for i in 1..number_of_VAR loop
cur_VAR:=substr(temp_VAR,1,3);
INSERT INTO TEST_ST (LONG_ST_NUM)
VALUES ( cur_VAR);
temp_VAR:= substr(temp_VAR,5);
end loop;
else
dbms_output.put_line('Rules problem for LONG_STRING: ' || rec.LONG_STRING);
end if;
end loop;
close c_1;
end;

/

Thanks...
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 8 2011
Added on Nov 8 2011
14 comments
20,247 views