The STUFF function in SQL Server is used to insert a substring into a string by deleting a specified number of characters from the original string starting at a specified position. The syntax for the STUFF function is as follows:
STUFF ( string_expression , start , length , replace_with_expression )
- string_expression: The original string.
- start: The starting position in the string where the deletion begins. The first character in the string is at position 1.
- length: The number of characters to delete from the original string, starting at the position specified by start.
- replace_with_expression: The substring that will be inserted into the original string.
Example
Let's say you have the string 'Hello, World!' and you want to replace 'World' with 'SQL Server'. Here’s how you can use the STUFF function to accomplish this:
DECLARE @originalString VARCHAR(100) = 'Hello, World!';
DECLARE @replacementString VARCHAR(100) = 'SQL Server';
DECLARE @start INT = 8; -- Starting position of 'World'
DECLARE @length INT = 5; -- Length of 'World'
SELECT STUFF(@originalString, @start, @length, @replacementString) AS ResultString;
Explanation of the Example
- @originalString is 'Hello, World!'.
- We specify @start as 8 because 'W' in 'World' is the 8th character of the original string.
- The @length is set to 5, which is the number of characters in 'World'.
- We set @replacementString as 'SQL Server', which we want to insert in place of 'World'.
Result
This SELECT statement would return:
ResultString
----------------
Hello, SQL Server!
So, the STUFF function effectively replaces the substring 'World' with 'SQL Server' in the original string.