0

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?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Seyi
  • 9
  • 3

1 Answers1

3

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)

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • This needs more work, to cover the special cases in the "Remarks" paragraph of the documentation. For example: If the starting position is more than the length of the first string, STUFF() returns null. Your solution does not. Same if the "length" argument is negative, or 0. If the length to delete is longer than the first string, the first string must be deleted to the first character. Your solution doesn't do that. –  Nov 01 '21 at 22:09
  • Absolutely, @mathguy. That's minimum of all minimums. The OP is free to improve it. – Littlefoot Nov 02 '21 at 06:05