Sql server® 2012 t-sql fundamentals

70  

Microsoft SQL Server 2012 T-SQL Fundamentals

The 

STUFF

 Function

The STUFF function allows you to remove a substring from a string and insert a new substring instead.

Syntax

STUFF(string, pos, delete_length, insertstring)

This function operates on the input parameter string. It deletes as many characters as the number 

specified in the delete_length parameter, starting at the character position specified in the pos input 

parameter. The function inserts the string specified in the insertstring parameter in position pos. For 

example, the following code operates on the string ‘ xyz’, removes one character from the second 

character, and inserts the substring ‘abc’ instead.

SELECT STUFF('xyz', 2, 1, 'abc');

The output of this code is ‘xabcz’.

If you just want to insert a string and not delete anything, you can specify a length of 0 as the third 

argument.