The STUFF function in SQL Server is used to insert a substring into a string at a specified position. The function can also delete a specified number of characters from the original string starting at the specified position.
The syntax for the STUFF function is as follows:
STUFF ( string_expression , start , length , new_string )
- string_expression: The original string where you want to insert the new substring.
- start: The position in the original string where you want to begin the delete and insert operation. The first position in the string is 1.
- length: The number of characters to delete from the original string, starting from the position specified in start.
- new_string: The substring that you want to insert into the original string.
Example
Suppose you have the following string: 'Hello World', and you want to insert the substring 'Beautiful ' at position 7, while also deleting 5 characters (which is World).
Here's how you would use the STUFF function:
SELECT STUFF('Hello World', 7, 5, 'Beautiful ')
In this example:
- string_expression is 'Hello World'
- start is 7 (where you want to start inserting)
- length is 5 (to delete the word World)
- new_string is 'Beautiful ' (what you want to insert)
The result of this query would be:
'Hello Beautiful '
The portion of the original string ('World') that starts at position 7 is deleted, and the substring 'Beautiful ' is inserted in its place.
Note
If the length parameter is set to 0, the STUFF function will simply insert the new_string at the start position without deleting any characters. For example:
SELECT STUFF('Hello World', 7, 0, 'Beautiful ')
This would result in:
'Hello Beautiful World'