Overview
The STUFF function is used to insert a string into another string at a specified start location and with a specified length.
Explanation
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
Additional Information
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
