The STUFF function in SQL is used to insert a string into another string by replacing a specified length of characters in the original string with another string. The syntax for the STUFF function is as follows:
STUFF (string_expression , start , length , replaceWith_expression )
- string_expression: The original string.
- start: The starting position in the original string where you want to begin the insertion.
- length: The number of characters in the original string that you want to replace.
- replaceWith_expression: The string that you want to insert.
Example
Let's say we have the string "Hello World" and we want to insert "Beautiful " at position 7, replacing "World".
Here’s how you can use the STUFF function:
SELECT STUFF('Hello World', 7, 5, 'Beautiful ') AS Result;
Explanation:
- string_expression: 'Hello World'
- start: 7 (the position in the string where you want to start replacing; starting from 1)
- length: 5 (the number of characters to replace, which in this case is "World")
- replaceWith_expression: 'Beautiful '
Result:
The result will be:
Hello Beautiful
This example shows that "World" was replaced with "Beautiful ", and the final string is returned with the insert completed.