SQL Server STUFF() Function
The STUFF() function in SQL Server is used to modify a string by deleting a portion of it and then inserting a new string into the same location. It's a powerful tool for string manipulation.
STUFF(): Definition and Usage
STUFF() allows for more precise string editing compared to the REPLACE() function, which replaces all occurrences of a substring. STUFF() lets you target a specific location within the string for the deletion and insertion.
Related Function
For replacing all occurrences of a substring, see the REPLACE() function.
Syntax
Syntax
STUFF(string, start, length, new_string)
Parameter Values
| Parameter | Description |
|---|---|
string |
The original string. This is required. |
start |
The starting position for the deletion (1-based index). This is required. |
length |
The number of characters to delete. This is required. |
new_string |
The new string to insert at the start position. This is required. |
Examples
Replacing Part of a String
This example deletes the first three characters ('SQL') and inserts 'HTML' at the beginning.
Syntax
SELECT STUFF('SQL Tutorial', 1, 3, 'HTML');
Output
HTML Tutorial
Inserting into a String
This deletes one character at position 13 and inserts ' is fun!'
Syntax
SELECT STUFF('SQL Tutorial!', 13, 1, ' is fun!');
Output
SQL Tutorial is fun!