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. |