RSS Feed

Difference between Replace & Stuff

Difference between Replace & Stuff

Stuff Replace
Definition:
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.
Syntax:
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.
Examples:
Example1
SELECT STUFF('abcdefg',4,2,'XXX')
Result:
abcXXXfg

Example2
SELECT STUFF(STUFF('SQLServer2012', 4,0,'-'), 11,0, '-')
Result:
SQL-Server-2012

Example3
SELECT STUFF('abcdefg',1,7,'XXX')
Result:
XXX

Example 4
SELECT STUFF('abcdefg',2,3,Null)
Result:
aefg
Example 1
SELECT REPLACE('abc123abc123','abc','XXX');
Result:
XXX123XXX123

Example 2
SELECT REPLACE('abc123abc123','abc123abc123','XXX');
Result:
XXX

Example 3
SELECT REPLACE('abc123abc123','123',Null);
Result:
NULL
Availability:
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.