The STUFF function in SQL Server is a powerful yet under-utilized utility that can come in handy in various scenarios. This function allows you to replace specific characters within a string with the contents of another string. It requires you to specify the starting position and the number of characters to replace.

You might be wondering why the function is not called REPLACE. Well, it turns out that T-SQL already has a REPLACE function, so STUFF became the chosen name for this particular function.

Let’s take a look at an example to understand how the STUFF function works:

DECLARE @STRING NVARCHAR(20) = 'Your Welcome';

SELECT STUFF(@STRING, 4, 1, '''re') AS Correction;

In this example, we have a string “Your Welcome” and we want to replace the character at position 4 with the string “‘re”. The result of this query will be “You’re welcome”.

The STUFF function can also be used with the result of SELECT statements. This allows you to manipulate strings dynamically based on the data in your database. Here’s an example:

SELECT STUFF((SELECT 'Your Welcome'), 4, 1, (SELECT '''re')) AS [Correction(Again)];

In this case, we are using the STUFF function with a subquery to replace the character at position 4 with the string “‘re”. The result will be the same as before, “You’re welcome”.

One interesting feature of the STUFF function is that the replacement string can be empty. This means that you can effectively remove a character from the original string. This can be particularly useful when dealing with comma-separated strings. Here’s an example:

SELECT STUFF((SELECT ',Martin,Julie,John,Bert,Stephen,Jean,Claire'), 1, 1, '') AS [First Comma removed];

In this example, we have a comma-separated string and we want to remove the leading comma. By specifying the starting position as 1 and the number of characters to replace as 1, we effectively remove the first comma from the string.

The STUFF function is a versatile tool that can be used in various scenarios to manipulate strings in SQL Server. Whether you need to replace characters, remove characters, or dynamically modify strings based on data, the STUFF function can help you achieve your goals.

Next time you find yourself needing to manipulate strings in SQL Server, consider giving the STUFF function a try. It might just become one of your go-to tools in your SQL Server toolbox.

Hope you found this exploration of the STUFF function useful. Have a great day!

Cheers,

Martin