Re: PL SQL

That works, but you can be a little more elegant:

0. Before you start the loop, put a final comma at the right end of the list

   (123,234,345,..., 765) -> (123,234,345,...,765,) 1. In your "while" statement, test for non zero string length 2. Inside the loop, find the leading substring of numbers starting at the left of the big string, up to including the comma which terminates the substring on its own right end.
3. Save the substring fragment you've just found , remove the trailing comma and Convert the digit string as usual
4. Remove the substring (Which you've just found and converted)

   from the original string leaving you first with (234, ... 765,) then (345,...,765,)

   then eventually (765,) and at last with the null string (zero length) which   satisfies the "while" condition and ends the loop.

cf wrote:

> Exemple :
>
> SET SERVEROUTPUT ON SIZE 300000
> declare
> cpt number := 0;
> pos1 number;
> pos2 number;
> val varchar2(20);
> val1 varchar2(20);
> line varchar2(100) := '123,455,744,855,886,96,14523,4578,25';
> begin
> while true loop
> cpt := cpt + 1;
> pos1 := instr(line,',',1,cpt);
> pos2 := instr(line,',',1,cpt+1);
>
> if pos1 = 0 Then exit;
> Elsif pos2 = 0
> Then val := substr(line,pos1+1,length(line)-pos1);
> Else val := substr(line,pos1+1,pos2-pos1-1);
> end if;
>
> if cpt = 1
> Then val1 := substr(line,1,pos1-1);
> Dbms_Output.Put_Line('1 -- POS:1-'||to_char(pos1)||' -- VAL:
> '||val1);
> End if;
>
> Dbms_Output.Put_Line(to_char(cpt+1) ||' -- POS:'||
> to_char(pos1)||'-'||to_char(pos2)||' -- VAL: '||val);
>
> end loop;
> end;
> /
> 1 -- POS:1-4 -- VAL: 123
> 2 -- POS:4-8 -- VAL: 455
> 3 -- POS:8-12 -- VAL: 744
> 4 -- POS:12-16 -- VAL: 855
> 5 -- POS:16-20 -- VAL: 886
> 6 -- POS:20-23 -- VAL: 96
> 7 -- POS:23-29 -- VAL: 14523
> 8 -- POS:29-34 -- VAL: 4578
> 9 -- POS:34-0 -- VAL: 25
> Procédure PL/SQL terminée avec succès.
>
> "cf" <news_at_sitinfo.com> a écrit dans le message news:
> 3c206207$0$24010$4d4eb98e_at_read.news.fr.uu.net...
> > Use function instr :
> > Ex:
> >
> > select instr('123,455,744,855,886,96,14523,4578,25',',',1,1) from dual
> > -> 4
> > select instr('123,455,744,855,886,96,14523,4578,25',',',1,8) from dual
> > -> 34
> > select instr('123,455,744,855,886,96,14523,4578,25',',',1,9) from dual
> > -> 0
> >
> > Christophe.
> >
> > "s Lehaire" <s.lehaire_at_meilleuregestion.com> a écrit dans le message news:
> > 9vplfs$12i$1_at_reader1.imaginet.fr...
> > > Hi,
> > > (I'm french guy so excuse for my english)
> > >
> > > I've got a little problem with my procedure
> > > I receive one string : number seprated by comas
> > > I need to update my table with those number but I don't know how many
> > number
> > > I have.
> > > so can you explain me how to do?
> > >
> > >
> >
> >
Received on Fri Dec 28 2001 - 14:55:18 CST