Late Night Dev

The STUFF Function is used to replace one string in your data with another string.  It deletes a specified length of characters from a string beginning at a specified start position and replaces them with another string beginning at the start position.  The STUFF is very useful when you wish to only replace one specific string as opposed to the REPLACE function which replaces every occurence of the string in the data.

The syntax for STUFF is -

STUFF ( character_expression , start , length , replacement_characters )

The STUFF function can be used to accomplish many useful tasks, below is an example using STUFF to insert dashes into a social security number.

DECLARE @vSnum VARCHAR(11)

SELECT @vSnum =‘123456789′

SELECT @vSnum,STUFF(STUFF(@vSnum,4,0,‘-’),7,0,‘-’)

For more details on the STUFF function visit http://msdn.microsoft.com/en-us/library/ms188043.aspx.

Tweet This Post