SQL Server STUFF Function

By: Daniel Calbimonte

The STUFF function is used to insert a string into another string at a specified start location and with a specified length.

Syntax

STUFF(expression, startPosition, length, expression_to_replace)

Parameters

• expression - this is the string we want to add to.
• startPosition – this the position number of the expression where to insert new string.
• length – this is the number of characters to replace in to the expression.
• expression_to_replace – the string that will be inserted. If you use NULL, nothing is inserted and a portion of the expression is replaced with nothing.

Simple STUFF Example

The following example will insert the word "MSSQLTIPS" starting at position 7 and replace 5 characters, which would be the word "world".

SELECT STUFF('Hello world', 7, 5, 'MSSQLTIPS') as msg 

STUFF Using Negative Numbers

If the startPosition or length is negative, the STUFF function will return a NULL value.

SELECT STUFF('Hello world', -7, 5, 'MSSQLTIPS') as msg 

Use STUFF Function to Insert Values from a Table Column

The following example uses the PATINDEX function to find "@" in the email and overwrites it with "&".

SELECT STUFF([EmailAddress], PATINDEX('%@%',EmailAddress) , 1, '&') as msg 
FROM [Person].[EmailAddress]

Using STUFF to Mask Data

Here is another example where we wipe out the first part of the email address and replace with "*".

SELECT EmailAddress, STUFF(EmailAddress,1,CHARINDEX('@',EmailAddress)-1,REPLICATE('*',CHARINDEX('@',EmailAddress)-1))
FROM [Person].[EmailAddress]

Using STUFF to Add to a String

In this example, we are adding the word "simple" to our first string and not replacing anything.

DECLARE @string varchar(50) = 'This is a test to see how STUFF works.'

DECLARE @stringToInsert varchar(50) = 'simple '

SELECT STUFF(@string, PATINDEX('%test%',@string), 0, @stringToInsert) as output

Related Articles

• SQL CHARINDEX
• SQL CONCAT
• SQL PATINDEX
• SQL REPLICATE
• SQL REPLACE