This function can be used for delete a certain length of the string and insert a new string in the deleted place.
STUFF ( InputString, start, length, ReplacedString )
STUFF (String, StartPos, LengthofReplaceChar, ReplaceString)
String - String to be overwritten
StartPos - Starting Position for overwriting
LengthofReplaceChar - Length of replacement string
ReplaceString - String to overwrite
Select STUFF ("this is a test", 7, 2, "was")
-- Result - this was a test
This function replaces all the occurrences of a string expression with a new string within an input string.
REPLACE ( InputString , StringToReplace , NewString )
REPLACE (String, StringToReplace,StringTobeReplaced)
String - Input String
StringToReplace - The portion of string to replace
StringTobeReplaced - String to overwrite
Select REPLACE ("This is a test and it is successful!","is","was")
This was a test and it was successful!
Both STUFF and REPLACE are used to replace characters in a string.
Result : abcabcabc
We cannot replace a specific occurrence of "def" using REPLACE.
select stuff('defdefdef',4, 3,'abc')
where 4 is the character to begin replace from and 3 is the number of characters to replace.