What is the best way to replicate the STUFF() function from T-SQL in PL/SQL?

Question

I'm trying to replicate the behavior of the STUFF() function from Transact SQL in Oracle. I assume both the CONCAT and SUBSTR functions will be involved, but I can't figure out a clever way to do it without writing too much code.

Does anyone know a fancy way to achieve this?

Answer 1

As there's no such built-in function, as you said - you'll have to write one for your own, using concatenation and substr function. Something like this:

SQL> create or replace function stuff
  2    (par_str in varchar2,
  3     par_start in number,
  4     par_length in number,
  5     par_replace_with in varchar2
  6    )
  7    return varchar2
  8  is
  9  begin
 10    return substr(par_str, 1, par_start - 1) ||
 11           par_replace_with ||
 12           substr(par_str, par_start + par_length);
 13  end stuff;
 14  /

Function created.

SQL> select stuff('abcdef', 2, 3, 'ijklmn') result from dual;

RESULT
--------------------------------------------------------------------------------
aijklmnef

SQL>

---

(Simulated using STUFF T-SQL documentation)