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
