Replace One character in string with multiple characters in loop - ORACLE

There is a special PL/SQL function UTL_LMS.FORMAT_MESSAGE:

You can use use it in your INLINE pl/sql function:

with function format(
    str in varchar2
   ,s1 in varchar2 default null
   ,s2 in varchar2 default null
   ,s3 in varchar2 default null
   ,s4 in varchar2 default null
   ,s5 in varchar2 default null
   ,s6 in varchar2 default null
   ,s7 in varchar2 default null
   ,s8 in varchar2 default null
   ,s9 in varchar2 default null
   ,s10 in varchar2 default null
   ) return varchar2 
   as
begin
   return utl_lms.format_message(replace(str,'[$]','%s'),s1,s2,s3,s4,s5,s6,s7,s8,s9,10);
end;
select format('Thi[$] is a strin[$] I am [$]ew to Or[$]cle', 's','g','n','a') as res
from dual;

Result:

RES
-------------------------------------
This is a string I am new to Oracle

Here is a hand-rolled solution using a recursive WITH clause, and INSTR and SUBSTR functions to chop the string and inject the relevant letter at each juncture.

with rcte(str, sigils, occ) as (
  select 'Thi[$] is a strin[$] I am [$]ew to Or[$]cle' as str
          , 'sgna' as sigils
          , 0 as occ 
  from dual
  union all
  select substr(str, 1, instr(str,'[$]',1,1)-1)||substr(sigils, occ+1, 1)||substr(str, instr(str,'[$]',1,1)+3) as str
         , sigils
         , occ+1 as occ
  from rcte
  where occ <= length(sigils)
)
select * 
from rcte
where occ = length(sigils)

Here is a working demo on db<>fiddle.

However, it looks like @sayanm has provided a neater solution.

Consider this method that lets the lookup values be table-based. See the comments within. The original string is split into rows using the placeholder as a delimiter. Then the rows are put back together using listagg, joining on it's order to the lookup table.

Table-driven using as many placeholders as you want. The order matters though of course just as with the other answers.

-- First CTE just sets up source data
WITH tbl(str) AS (
  SELECT 'Thi[$] is a strin[$] I am [$]ew to Or[$]cle' FROM dual
),
-- Lookup table.  Does not have to be a CTE here, but a normal table
-- in the database.
tbl_sub_values(ID, VALUE) AS (
  SELECT 1, 's' FROM dual UNION ALL
  SELECT 2, 'g' FROM dual UNION ALL
  SELECT 3, 'n' FROM dual UNION ALL
  SELECT 4, 'a' FROM dual
),
-- Split the source data using the placeholder as a delimiter
tbl_split(piece_id, str) AS (
  SELECT LEVEL AS piece_id, REGEXP_SUBSTR(t.str, '(.*?)(\[\$\]|$)', 1, LEVEL, NULL, 1) 
  FROM tbl T
  CONNECT BY LEVEL <= REGEXP_COUNT(t.str, '[$]') + 1
)
-- select * from tbl_split;
-- Put the string back together, joining with the lookup table
SELECT LISTAGG(str||tsv.value) WITHIN GROUP (ORDER BY piece_id) STRING
FROM tbl_split ts
  LEFT JOIN tbl_sub_values tsv
    ON ts.piece_id = tsv.id;

STRING                                                                          
--------------------------------------------------------------------------------
This is a string I am new to Oracle