I am having trouble with replace function inside a function. If I do the same replace function outside it works fine, Following example clearly explains my problem. Can you please help?
create table str_table
( mystr varchar(1000));
insert into str_table values ( 'This road is INDEFLY CLSD');
insert into str_table values('That is INDEFLY open');
insert into str_table values('That is a CLSD road');
select * from str_table;
create table lookup
( code varchar(100),
codeword varchar(100));
insert into lookup values( 'INDEFLY','indefinitely');
insert into lookup values( 'CLSD','closed');
select 'This road is INDEFLY CLSD', test_repl('This road is INDEFLY CLSD INDEFLY') replaced_str
from str_table;
RESULT SET " This road is INDEFLY CLSD;This road is indefinitely closed INDEFLY "
Regards