DBTalk – Stuff vs Replace

Why would you use “Stuff” instead of “Replace?”

Good question.  Both perform a similar function – replace a string with another string (or no string at all), but Replace has a simpler syntax.

The Replace function looks like this:

select replace('abcdef','bc','xx')

result: axxdef

No locations to find, just replace the string.  However, what if we had a string with two occurrences of the string, like:

select replace('abcabcabc','abc','123')

result: 123123123

If we only wanted to replace the first one, Replace wouldn’t work, since it always replaces ALL occurrences of the string. But Stuff would, since it only replaces the string it finds at the starting location we tell it for the number of chars we want it to replace. 

select stuff('abcabcabc',4, 3,'123')

result: abc123abc

Since I tend to prefer explicit control of strings (to reduce the possibility of unintended substitutions), I will usually grab Stuff rather than Replace.  The overhead is probably slightly less for Stuff since the function already knows where to look for the value, but I don’t consider that a deal breaker.