Difference between STUFF function and Replace Function in SQL ?

The STUFF function is used to overwrite the characters of string.

The Replace function is used to replace the occurance of a particular string with the specified string in all occurances.


STUFF Syntax:

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


Declare @OriginalString Varchar(100)
Declare @ResultString Varchar (100)
Declare @ReplaceString Varchar(100)

SET @OriginalString = 'ALAMO'
SET @ReplaceString = 'Welcome '

SET @ResultString = SELECT STUFF(@OriginalString, 1,7, @ReplaceString)

Print 'Result: ' + @ResultString




Output

It will erase the letter A(First Letter) and inject 'Welcome ' in that position

Result: Welcome Lamo


REPLACE Syntax:

REPLACE (String, StringToReplace,StringTobeReplaced)

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


Example


Declare @OriginalString Varchar(100)
Declare @ResultString Varchar (100)
Declare @ReplaceString1 Varchar(100)
Declare @ReplaceString2 Varchar(100)

SET @OriginalString = 'ALAMO'
SET @ReplaceString1 = 'A'
SET @ReplaceString2 = 'V'


SET @ResultString = SELECT REPLACE(@OriginalString, @ReplaceString1, @ReplaceString2)

Print 'Result: ' + @ResultString



Output

It will replace the letter A in the whole string

Result: VLVMO