STUFF Function in SQL Server with Example

SQL Server provides various built-in functions to manipulate and process data efficiently. One such function is the STUFF function, which is commonly used to modify string values by inserting or replacing a portion of the string. This article explores the STUFF function in SQL Server with detailed explanations, syntax, examples, and practical use cases.

For a more detailed explanation, you can refer to SQL Server STUFF() Function With Example.

What is the STUFF Function in SQL Server?

The STUFF function in SQL Server is used to delete a specified length of characters from a given string and then insert another string at the specified position. This function is particularly useful in scenarios where string modification is required.

Syntax of STUFF Function

STUFF (original_string, start_position, length_to_delete, string_to_insert)

Parameters:

• original_string: The string in which modifications will be made.
• start_position: The position in the string where the modification begins.
• length_to_delete: The number of characters to delete from the original string.
• string_to_insert: The new string to be inserted at the specified position.

STUFF Function Example in SQL Server

Basic Example:

Let's replace a portion of a string using the STUFF function.

SELECT STUFF('Hello Nikunj Satasiya', 7, 6, 'World') AS ModifiedString;

Output:

Explanation:

• The function starts modifying from position 7.
• It deletes 6 characters (Nikunj).
• It inserts 'World', resulting in 'Hello World Satasiya'.

Using STUFF Function with Table Data

Consider a table named Authors with the following data:

We need to update author names by replacing the last name with 'Updated'.

SELECT AuthorID, STUFF(Name, CHARINDEX(' ', Name) + 1, LEN(Name) - CHARINDEX(' ', Name), 'Updated') AS ModifiedName
FROM Authors;

Output:

Explanation:

• CHARINDEX(' ', Name) finds the position of the space.
• The function replaces the last name with 'Updated'.

Practical Use Cases of STUFF Function

1. Formatting Phone Numbers

SELECT STUFF(STUFF('9999999999', 4, 0, '-'), 8, 0, '-') AS FormattedNumber;

Output:

2. Removing and Inserting Characters in Usernames

SELECT STUFF('nikunj_satasiya_123', 7, 9, 'admin') AS ModifiedUsername;

Output:

3. Generating Comma-Separated Values

We can use STUFF to generate a CSV list of values:

SELECT STUFF((SELECT ',' + Name FROM Authors FOR XML PATH('')), 1, 1, '') AS AuthorList;

Output:

For similar string manipulation techniques, check out:

• Split Alphabets from Alphanumeric String In SQL
• Split Numbers From Alphanumeric String In SQL Server
• Concatenate value from multiple rows to a single string in SQL Server

Difference Between STUFF and REPLACE Functions

Conclusion

The STUFF function in SQL Server is a powerful tool for modifying string data by removing and inserting characters dynamically. It is widely used in formatting, data manipulation, and generating structured outputs. By understanding and implementing STUFF in various scenarios, SQL developers can enhance their query capabilities and improve data processing efficiency.