how to replace multiple strings together in Oracle

Question

I have a string coming from a table like "can no pay{1},as your payment{2}due on {3}". I want to replace {1} with some value , {2} with some value and {3} with some value .

Is it Possible to replace all 3 in one replace function ? or is there any way I can directly write query and get replaced value ? I want to replace these strings in Oracle stored procedure the original string is coming from one of my table I am just doing select on that table

and then I want to replace {1},{2},{3} values from that string to the other value that I have from another table

Answer 1

If there are many variables to replace and you have them in another table and if the number of variables is variable you can use a recursive CTE to replace them. An example below. In table fg_rulez you put the strings with their replacement. In table fg_data you have your input strings.

set define off;
drop table fg_rulez
create table fg_rulez as 
  select 1 id,'<' symbol, 'less than' text from dual
  union all select 2, '>', 'great than' from dual
  union all select 3, '$', 'dollars' from dual
  union all select 4, '&', 'and' from dual;
drop table fg_data;
create table fg_Data AS(
   SELECT 'amount $ must be < 1 & > 2' str FROM dual
   union all
   SELECT 'John is >  Peter & has many $' str FROM dual
   union all
   SELECT 'Eliana is < mary & do not has many $' str FROM dual

   );


WITH  q(str, id) as (
  SELECT str, 0 id 
  FROM fg_Data 
     UNION ALL
  SELECT replace(q.str,symbol,text), fg_rulez.id
  FROM q 
  JOIN fg_rulez 
    ON q.id = fg_rulez.id - 1
)
SELECT str from q where id = (select max(id) from fg_rulez);

So, a single replace.

Result:

amount dollars must be less than 1 and great than 2 
John is great than Peter and has many dollars 
Eliana is less than mary and do not  has many dollars

The terminology symbol instead of variable comes from this duplicated question.

Oracle 11gR2

Answer 2

Although it is not one call, you can nest the replace() calls:

SET mycol = replace( replace(mycol, '{1}', 'myoneval'), '{2}', mytwoval)