String munipulation

[coolmandba]

Dear all,

I have a sentence like below:

eg.

"I have a %1, it color is %2, and it costs %3"

This sentence is retrived from a varchar2 column of a table, say if the column is called 'sentence_text',and I would like to replace all %(n) with the input parameters from a procedure. Say for example, the I'm calling the proedure as:

exec testsentence('car','red','$80000');

and the sentence retrieved from 'sentence_text' column could have one, two or more %(n), so how a procedure can be written to handle this? Please advise with codes if possible, thanks.

[kmesser]

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]

[coolmandba]

Thanks kmesser, I'll try that and let you know how it goes.

[kmesser]

For a single line of sql code, you could do something like:

declare
pct1 varchar2(100) := 'Daddy';
pct2 varchar2(100) := 'Mommy';
pct3 varchar2(100) := 'Baby';
x varchar2(1000);
begin
select replace(replace(replace('%1 bear is the largest, %2 bear is in the middle and %3 bear is smallest','%1',pct1),'%2',pct2),'%3',pct3) into x from dual;
dbms_output.put_line(x);
end;
/

I did this from the SQL*PLUS command prompt, so I declared my variables in the anonymous block. Obviously, you could use variables available in the session to which you are connected. The limitation is the number of REPLACE statements you wish to/can nest. I don't know the functional limit, but it gets tougher to manage the code when the nesting goes beyond a certain level.

Good luck!