SQL Server | STUFF() Function

In SQL Server, the STUFF() function is used to replace a part of a string with another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string at the start position.

Syntax:

STUFF(character_expression, start, length, replaceWith_expression)

character_expression: The original string.
start: The starting position where characters are to be removed and insertion is to take place.
length: The number of characters to be removed. If this value is set to 0, the function effectively works like an "insert" without removal.
replaceWith_expression: The string to be inserted into character_expression.

Example:

Suppose you have a string "HelloWorld" and you want to replace "World" with "SQL Server". Here's how you'd use the STUFF() function:

SELECT STUFF('HelloWorld', 6, 5, 'SQL Server') AS Result;

Output:

HelloSQL Server

Practical Usage:

A common usage for STUFF() in SQL Server is in combination with the FOR XML PATH clause to concatenate row values into a single string. For example, if you have a table of names and you want to combine them into a comma-separated list:

DECLARE @NamesTable TABLE (Name VARCHAR(100));
INSERT INTO @NamesTable VALUES ('John'), ('Jane'), ('Doe');

SELECT 
    STUFF(
        (SELECT ',' + Name FROM @NamesTable FOR XML PATH('')), 
        1, 1, '') AS ConcatenatedNames;

Output:

John,Jane,Doe

In this example, FOR XML PATH('') is used to concatenate row values, and the STUFF() function removes the leading comma.

The STUFF() function is quite versatile and is commonly used for string manipulations in SQL Server.

Using STUFF() to replace characters in SQL Server:
- STUFF() can be used to replace characters within a string.
```
SELECT STUFF('Hello World', 7, 5, 'Universe') AS replaced_string;
```
Concatenating strings with STUFF() in SQL Server:
- STUFF() can be used for concatenation by replacing a portion of the original string.
```
SELECT STUFF('Hello ', 7, 0, 'World') AS concatenated_string;
```

STUFF() vs REPLACE() in SQL Server:

STUFF() and REPLACE() can be used for similar tasks, but STUFF() is more versatile in replacing specific portions.

-- Using STUFF()
SELECT STUFF('Hello World', 7, 5, 'Universe') AS replaced_string;

-- Using REPLACE()
SELECT REPLACE('Hello World', 'World', 'Universe') AS replaced_string;

Removing characters with STUFF() in SQL Server:
- STUFF() can be used to remove characters by specifying a length of 0.
```
SELECT STUFF('Hello World', 7, 5, '') AS removed_string;
```
Dynamic string manipulation using STUFF() in SQL Server:
- STUFF() can be used dynamically based on the length of the substring to be replaced.
```
DECLARE @start INT = 7, @length INT = 5;
SELECT STUFF('Hello World', @start, @length, 'Universe') AS dynamic_string;
```
Nested usage of STUFF() in SQL Server queries:
- STUFF() can be nested for more complex string manipulations.
```
SELECT STUFF(STUFF('Hello World', 7, 5, 'Universe'), 1, 5, 'Greetings') AS nested_string;
```

STUFF() function for XML string manipulation in SQL Server:

STUFF() is useful for XML string manipulation, such as adding or replacing elements.

DECLARE @xml XML = '<root><item>OldValue</item></root>';
SET @xml.modify('replace value of (/root/item/text())[1] with "NewValue"');
SELECT @xml AS modified_xml;