The STUFF function in SQL Server is used to insert a string into another string at a specified position while removing a specified length of characters from the original string. The basic syntax of the STUFF function is:
STUFF ( string_expression , start , length , replace_with )
- string_expression: The original string to be modified.
- start: The 1-based starting position in the original string where the insertion will occur.
- length: The number of characters to delete from the original string starting at the position specified by start.
- replace_with: The string that will be inserted into the original string.
Example Usage
Suppose you have the following string: 'Hello World', and you want to insert the string 'Beautiful ' at position 7, effectively replacing the word 'World'. Here's how you can do it:
DECLARE @original_string VARCHAR(100) = 'Hello World';
DECLARE @insert_string VARCHAR(100) = 'Beautiful ';
DECLARE @start_position INT = 7;
DECLARE @length INT = LEN('World'); -- Length of the string to remove
SELECT STUFF(@original_string, @start_position, @length, @insert_string) AS Result;
Explanation of the Example:
- The original string is 'Hello World'.
- You start inserting at position 7, which is the beginning of 'World'.
- You specify a length equal to the length of 'World', which is 5.
- The string you want to insert is 'Beautiful '.
The result of this operation would be:
Result
--------------------
Hello Beautiful
This means that the original string 'Hello World' has been modified to include 'Beautiful ' starting at position 7, and 'World' has been removed.