The STUFF() function in SQL Server is used to insert a string into another string, or to delete a portion of a string and replace it with another string. It allows you to modify a string by removing part of it and adding new content in its place.
Syntax:
STUFF(string_expression, start_position, length, replacement_string)
- string_expression: The original string where the operation will be applied.
- start_position: The position in the string where the deletion will begin. The first character in the string is at position 1.
- length: The number of characters to delete from the original string, starting from the
start_position.
- replacement_string: The string that will replace the deleted portion. If this is an empty string (
''), it will simply remove the characters.
Behavior:
- The function deletes
length characters from the string starting at start_position, and then inserts replacement_string in place of the deleted characters.
- If the
replacement_string is longer or shorter than the deleted portion, the string will still be modified accordingly.
Example 1: Basic Usage (Replace Part of a String)
Let's say you have a string 'abcdef' and you want to replace the part of the string starting at position 3 (the character 'c') with 'XYZ':
SELECT STUFF('abcdef', 3, 3, 'XYZ') AS ModifiedString;
Result:
abXYZef
- The string starts at position 3, deletes 3 characters ('cde'), and inserts
'XYZ' in place.
Example 2: Removing Characters without Insertion
If you want to remove part of the string without inserting anything, you can pass an empty string ('') as the replacement_string. For example, to remove the characters starting from position 4 in the string 'abcdef':
SELECT STUFF('abcdef', 4, 3, '') AS ModifiedString;
Result:
abc
- The string starts at position 4 and deletes 3 characters ('def'), leaving
'abc'.
Example 3: Inserting Text without Deleting Anything
You can also insert text without deleting anything by specifying 0 for the length parameter. For example, to insert 'XYZ' at position 4 in the string 'abcdef':
SELECT STUFF('abcdef', 4, 0, 'XYZ') AS ModifiedString;
Result:
abcXYZdef
- No characters are deleted (since
length is 0), and 'XYZ' is inserted at position 4.
Example 4: Edge Case with Position Beyond String Length
If the start_position is greater than the length of the string, the STUFF() function will insert the replacement_string at the end of the original string, as there is nothing to delete. For example:
SELECT STUFF('abcdef', 10, 3, 'XYZ') AS ModifiedString;
Result:
abcdefXYZ
- Since position 10 is beyond the length of the string,
'XYZ' is appended to the end.
Example 5: Replace Multiple Occurrences (Using STUFF with FOR XML PATH)
STUFF() is often used in conjunction with other SQL functions to concatenate values. A common pattern is using it to concatenate multiple rows into a single string. Here’s how you might use STUFF() to concatenate multiple values from rows in a column:
SELECT STUFF(
(SELECT ',' + ColumnName
FROM TableName
FOR XML PATH('')), 1, 1, '') AS ConcatenatedString;
- This query combines all values in
ColumnName from the table TableName into a comma-separated string.
- The
FOR XML PATH('') part generates the XML string, and the STUFF() function removes the leading comma from the concatenated result.
Summary
STUFF() is a versatile function in SQL Server for modifying strings.- It allows you to delete a portion of a string and replace it with another string.
- You can also use it to insert data at a specific position or remove part of a string without replacing it.
- It's commonly used for tasks like string manipulation and data formatting.
