SQL STUFF Function

The SQL STUFF function insert one string inside another string. The syntax of the SQL Server STUFF Function is

SELECT STUFF (Character_Expression, Starting_Position, Length, New_String)
FROM [Source]
  • Character_Expression: String on which you want to insert New_String using the SQL Server stuff function.
  • Starting_Position: From which index position, you want to start inserting the New_String characters.
  • Length: How many characters you want to delete from the Character_Expression. SQL Stuff Function 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

SQL STUFF FUNCTION

SQL STUFF Function Example 1

The String STUFF function is used to stuff the new string inside the old string. It show you multiple ways to use the String STUFF function.

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 'SQL STUFF' 

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

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

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

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

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

We assigned start position = 6, ending position = 0, and the fourth argument is SQL. So, this String Function will delete 0 characters from index position 6 and inserts SQL at index position 6

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

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

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

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

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

In the next line, We set the second argument like 20, which is greater than the string length so, STUFF function will return NULL

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

SQL STUFF Function Example 2

The Sql Server STUFF function also allows you to add new string to 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, the STUFF Function 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 positive value or see the next example.

SQL STUFF FUNCTION 2

Changing Email Ids using SQL STUFF Function

In this sql string stuff example, We are going to change the email ids present in the Email column using the SQL Server 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 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 want to replace characters before this symbol.

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

Below stuff function statement will delete the characters from index position 1 to @ symbol and then insert [Last Name] data in that place using STUFF

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