Here is A solution. No doubt wiser people on this board could help you more . . .

Just pass in two strings:
1st - the string containing the %1 . . .
2nd - the comma delimited string of replacement values.


create or replace function replacecodes (sentence_in varchar2 := NULL, replace_in varchar2 := NULL) return varchar2 as

stringcount number;
v_sentence varchar2(1000);
v_replace varchar2(1000);

begin
if sentence_in is null or replace_in is null then
return 'Invalid Input';
end if;
v_sentence := sentence_in;
v_replace := replace_in;
stringcount := 0;
while v_replace is not null loop
stringcount := stringcount + 1;
select replace(v_sentence,'%'||to_char(stringcount),nvl(rtrim(ltrim(substr(v_replace,1,instr(v_replace,',')-1))),rtrim(ltrim(v_replace)))) into v_sentence from dual;
if instr(v_replace,',')=0 then
v_replace := NULL;
else
v_replace := substr(v_replace,instr(v_replace,',')+1);
end if;
end loop;
return v_sentence;
end;
/


try:

SQL>set serveroutput on
SQL>exec dbms_output.put_line(replacecodes('%1 bear is the largest, %2 bear is in the middle and %3 bear is smallest','Daddy, Mommy, Baby'));

You could also use this in a select statement, such as:

select replacecodes(myvarchar2field,'replacevalue1,replacevalue2') from mytable;

[Edited by kmesser on 04-17-2001 at 09:24 AM]