SQL STUFF Function (Transact SQL)

Description

The SQL STUFF function deletes a specified number of characters from a character expression and replaces them with another substring. In other words, it inserts a string into another string. The new string value, position to insert the new string, and the length of the new string are passed as arguments in the function.

SQL STUFF Usage Notes

1. Character expression as the first argument of STUFF function can be of a character or binary data type. It can be a constant value, a variable value, or a column of a database.
2. Start, as the second argument of the STUFF function, indicates the position in the character expression to start deleting and inserting the new character expression. 
   • It is an integer value and can be of bigint data type. 
   • The function returns a null value if the start value is zero, negative, or greater than the length of the character expression.

3. Length as the third argument of the SQL STUFF function indicates the number of characters to delete and the number of new characters to insert in the character expression.
   1. The data type of length can be bigint. 
   2. If the value of length is negative, the STUFF function returns a null value. 
   3. When the length is zero, the STUFF function inserts at location zero but deletes no character from the character expression. 
   4. If the length is greater than the length of the character expression, then the STUFF function deletes and inserts characters from the character expression equal to the length of the new character expression.

4. The new character expression as the final argument replaces the characters in the character expression equal to the Length.
   1. The value can be a constant, variable, or a column. 
   2. The data type can be either a character or a binary. 
   3. For a NULL value, the SQL STUFF function deletes characters without inserting any in the character expression.

Syntax

STUFF (character_expression, start, length, new_character_expression)

SQL STUFF Examples

We will use the AdventureWorks2019 database for all the examples.

Let’s start with a simple SQL query that deletes a part of the input string and replaces it with a new string value using the STUFF function.

/* Answer */
SELECT STUFF('replace this with that', 9, 4, 'that') StuffExample; 

This query deletes 4 characters starting from index 9 and inserts ‘that’ in place of them.

In the next example, we will delete and insert a substring greater than the length of the character expression.

/* Answer */
SELECT STUFF('STUFF', 2, 7, 'Replace') StuffExample; 

The query inserts a complete new string beginning from the start position.

The following example inserts a ‘Special’ value before every discount type in the Type column of Sales.SpecialOffer table.

/* Answer */
SELECT STUFF(TYPE, 1, 0,'Special ') AS SpecialOffer
FROM Sales.SpecialOffer  

The start position of one indicates the beginning of the input expression. The zero value of length argument indicates that no characters are deleted.

If we pass a null string as a new character expression, then the SQL STUFF function deletes the specified length of characters from the character expression beginning from the Start value. However, it does not insert any character. The following example explains this.

SELECT STUFF(Title, 1, 3,'') AS removeTitle  
FROM   Person.Person

The query deletes the starting three characters from the title column of the Person record. The blank output values show that the function deletes the column values and inserts nothing.

See Also

• Introduction to SQL Server’s String Functions
• SQL CHARINDEX
• Recommended book: SQL Practice Problems: 57 beginning, intermediate, and advanced challenges for you to solve using a “learn-by-doing” approach