Difference between Replace & Stuff
The Stuff function deletes a specified length of characters and inserts another set of characters in their place.
|Replace replaces all occurrences of a specified string value with another string value.|
STUFF(char_exp_1, starting_position, length, char_exp_2)
Returns a string that is composed by replacing part of char_exp_1 with char_exp_2.
starting_position specifies where the part of char_exp_1 that is to be replaced must begin. length specifies the number of characters from char_exp_1 that must be replaced.
If you just want to insert a string and not want to delete anything, then you can specify a length of 0 as the third argument.
|REPLACE ( string_expression , string_pattern , string_replacement )
string_expression is the string to be searched. string_pattern is the substring to be found. string_replacement is the replacement string.
SELECT STUFF(STUFF('SQLServer2012', 4,0,'-'), 11,0, '-')
Stuff was available in SQL Server 2000, 2005, 2008, 2008 R2 and 20012.
|Replace was available in SQL Server 2000, 2005, 2008, 2008 R2 and 20012.|