Difference Between STUFF() and Replace() in SqlServer

1. STUFF()

This function can be used for delete a certain length of the string and insert a new string in the deleted place.

Syntax

Code:
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


Example

Code:

Select STUFF ("this is a test", 7, 2, "was")

-- Result - this was a test


2. REPLACE()

This function replaces all the occurrences of a string expression with a new string within an input string.

Syntax:

Code:
REPLACE ( InputString , StringToReplace , NewString )

REPLACE (String, StringToReplace,StringTobeReplaced)

String - Input String
StringToReplace - The portion of string to replace
StringTobeReplaced - String to overwrite



Example,


Code:
Select REPLACE ("This is a test and it is successful!","is","was")

 Result :
This was a test and it was successful!

EX:

Both STUFF and REPLACE are used to replace characters in a string.

select replace('abcdef','ab','xx')
Result:   xxcdef
select replace('defdefdef','def','abc')
Result :   abcabcabc
We cannot replace a specific occurrence of "def" using REPLACE.

select stuff('defdefdef',4, 3,'abc')
Result:  defabcdef

where 4 is the character to begin replace from and 3 is the number of characters to replace.