Re: inserting comma-separated values with pl/sql

On Fri, 21 Feb 2003 20:22:42 +0100, "Christoph Seidel" <Christoph.Seidel1_at_gmx.de> wrote:

>Hi there,
>
>i have a stored proc which has a varchar parameter which contains a list of
>values, separated by comma.
>
>e.g.: 123,456,789
>
>what i wanna do is to separate the values and insert them into a table
>
>e.g.
>
>insert into t (f) values (123);
>insert into t (f) values (456);
>insert into t (f) values (789);
>
>Can this be done with pl/sql?

Of course it can. If it can be done within any other programming language you know, it can be done in pl/sql as well.

> And if i can be done, how?
>
>

Here's one of several ways to do it :

pos:= instr(StringWithCommas,',');

while pos > 0 loop
insert into t(f) values (substr(StringWithCommas,1,pos-1); StringWithCommas :=
substr(StringWithCommas,pos+1,length(StringWithCommas)-pos); pos:= instr(StringWithCommas,',');
end loop;

if replace(StringWithCommas,',') is not null then insert into t(f) values (StringWithCommas); end if;

The example does not handle invalid strings like ',,,,' or ',,hello,world, as you do not tell us how such strings should be handled, e.g. should ',,,,,'be considered as 7 times NULL's to be inserted or is just an empty string to be ignored ?

• Kenneth Koenraadt