What is STUFF and REPLACE Functon in SQL Server ?

STUFF() can be used to stuff a string into another string. It inserts the string at a given position, and deletes the number of characters specified from the original string.

DECLARE @string1 VARCHAR(20) = 'Microsoft Server'

DECLARE @string2 VARCHAR(20) = 'SQL Server 2005'

SELECT      @string1 + ' -> ' + STUFF(@string1, 11, 0, 'SQL ')

            AS 'String 1',

            @string2 + ' -> ' + STUFF(@string2, 15, 1, '8 R2')

            AS 'String 2'

Result :

                  String 1                                                                         String 2
Microsoft Server -> Microsoft SQL Server SQL Server 2005 -> SQL Server 2008 R2

REPLACE() replaces all the specified characters with new characters.

DECLARE @string3 VARCHAR(35) = 'sql 2005, sql 2008, sql 2008 r2'

SELECT @string3, REPLACE(@string3,'sql','SQL')

Result Set:

———————————–      ———————————–

sql 2005, sql 2008, sql 2008 r2   SQL 2005, SQL 2008, SQL 2008 r2

(1 row(s) affected)

However, it is not limited to same number of characters:

DECLARE @string3 VARCHAR(35) = '2008 R2'

SELECT @string3, REPLACE(@string3,'20','SQL Server 2')

Result Set:

————–       ————————

2008 R2              SQL Server 208 R2

(1 row(s) affected)