SQL Server STUFF Function


By:

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 simple example

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 
stuff function retuns null values

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]
STUFF and PATINDEX combined

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]
STUFF and PATINDEX combined

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
STUFF and PATINDEX combined

Related Articles


Last Update: 11/11/2021




Comments For This Article





download














vote


get free sql tips
agree to terms