SQL STUFF Function

The SQL Server STUFF function inserts one string inside another string. The syntax of the SQL STUFF function is shown below.

SELECT STUFF (Character_Expression, Starting_Position, Length, New_String)
FROM [Source]
  • Character_Expression: String on which you want to insert New_String using this SQL Stuff function.
  • Starting_Position: From which index position you want, start inserting the New_String characters.
  • Length: How many characters do you want to delete from the Character_Expression? It will start at Starting_Position and delete the specified number of characters from the Character_Expression.
  • New_String: New string you want to stuff inside the Character_Expression at Starting_Position

We use the below-shown data to explain the SQL Server Stuff function.

Employee Table

SQL STUFF Function Example

The SQL String STUFF function stores the new string inside the old one. It shows you multiple ways to use the STUFF.

DECLARE @Character_Expression varchar(50)
SET @Character_Expression = 'Learn Server' 

-- Starting Position = 6 and End Position = 0
SELECT STUFF (@Character_Expression, 6, 0, ' SQL') AS 'SQLSTUFF' 

-- Starting Position = 7 and End Position = 6
SELECT STUFF (@Character_Expression, 7, 6, 'SQL') AS 'SQLSTUFF' 

-- Starting Position = 1 and End Position = 5
SELECT STUFF (@Character_Expression, 1, 5, '') AS 'SQLSTUFF' 

-- Starting Position = 20 and End Position = 5
SELECT STUFF (@Character_Expression, 20, 5, 'SQL ') AS 'SQLSTUFF'
SQL STUFF Function Example 1

Below lines of Server code is used to declare string variable and assign the string data.

DECLARE @Character_Expression varchar(50)
SET @Character_Expression = 'Learn Server'

We assigned the start position = 6, and the ending position = 0. So, this String Function will delete 0 characters from index position 6 and insert text at index position 6

-- Starting Position = 6 and End Position = 0

We assigned starting position = 7, and end position = 6. So, this SQL stuff function will delete five characters from an index position 7 and inserts it at that position.

-- Starting Position = 7 and End Position = 6

Here, we have given the start position = 1, the end position = 5, and the fourth argument is empty. So, it deletes characters from index 1 to 5 and returns the remaining string.

-- Starting Position = 1 and End Position = 5
SELECT STUFF (@Character_Expression, 1, 5, '') AS 'SQLSTUFF'

In the next line, We set the second argument to 20, which is greater than the string length, so it will return a NULL.

-- Starting Position = 20 and End Position = 5
SELECT STUFF (@Character_Expression, 20, 5, 'SQL ') AS 'SQLSTUFF'

STUFF Example 2

The Sql Server STUFF function also allows you to add a new string to an old string in the column values. In this example, We insert [LastName] to the Email column at index position 1.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,STUFF (
              [Email]
             ,1
      ,0
      ,[LastName]
             ) AS [SQL SUBSTRING]
 FROM [Employe]

Here, it did not remove any characters from the Emails. It just added the last name to that because We used 0 as the third argument. If you want to remove a few characters, replace 0 with a positive value or see the next example.

SQL STUFF FUNCTION 2

Changing Email Ids using SQL STUFF Function

In this SQL Server example, We are going to change the email ids present in the Email column using the STUFF Function. The following query removes the characters before the @ symbol and inserts the [Last Name] column data.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,STUFF (
               [Email]
              ,1
       ,CHARINDEX('@',[Email]) - 1
       ,[LastName]
            ) AS [SQL SUBSTRING]
 FROM [Employe]
SQL STUFF FUNCTION to Change Email Ids 3

The below statement will return the index position of the @ symbol. It means CHARINDEX Function will check for the index position of @ symbol in each record. Next, we subtracted one from the index position because we wanted to replace characters before this symbol.

CHARINDEX ('@', [Email]) - 1

The below statement will delete the characters from index position 1 to @ symbol and then insert [Last Name] data in that place using this.

STUFF (
       [Email]
      ,1
      ,CHARINDEX('@',[Email]) - 1
      ,[LastName]
     ) AS [SQL SUBSTRING]
Categories SQL