SQL Server | STUFF() Function

The STUFF() function in SQL Server is a powerful string manipulation tool used to delete a specified length of characters from a string and insert another set of characters at a given starting position. This function becomes particularly useful in scenarios where complex string operations are required such as formatting output, merging data fields or generating custom reports.

In this article, We will learn about SQL Server STUFF() Function in detail and so on.

SQL Server STUFF() Function

• The STUFF() function in SQL Server is used to delete a specified length of characters from a string and then insert another set of characters at a specified starting position.
  
• STUFF() Function can play a crucial role in complex queries where string manipulation is needed. It’s particularly useful when dealing with formatted output or combining data fields in reports. 

Syntax:

STUFF (source_string, start, length, add_string)

Where:

• source_string: Original string to be modified. 
• start: The starting index from where the given length of characters will be deleted and new sequence of characters will be inserted.
• length: The numbers of characters to be deleted from the starting index in the original string. 
• add_string: The new set of characters (string) to be inserted in place of deleted characters from the starting index.

Note: It is not necessary to have the length of the new string and number of characters to be deleted the same. 

Examples of SQL Server STUFF() Function

Example 1:

Output:

Example 2:

Output:

Example 3:

Output:

Conclusion

The STUFF() function is an essential tool for string manipulation in SQL Server, providing flexibility and control over how strings are modified. Whether you're preparing formatted output or working with complex queries, STUFF() offers a straightforward solution to replace or insert character sequences within strings.