SQL Server String STUFF() Function

SQL Server STUFF() Function

The SQL Server STUFF() function is used to delete a specified length of characters from a string and then insert another set of characters at a specified starting position. This function is useful for modifying string data by replacing portions of a string.

Syntax

SELECT STUFF(string, start, length, new_string);

The STUFF() function takes four arguments:

  • string: The original string to be modified.
  • start: The starting position for deletion and insertion.
  • length: The number of characters to delete from the original string.
  • new_string: The string to insert into the original string.

Example SQL Server STUFF() Function Queries

Let's look at some examples of SQL Server STUFF() function queries:

1. Basic STUFF() Example

SELECT STUFF('Hello World', 7, 5, 'SQL') AS result;

This query deletes 5 characters starting from the 7th position in the string 'Hello World' and inserts 'SQL' at that position. The result will be:

result
----------
Hello SQL

2. STUFF() with Overlapping Insertion

SELECT STUFF('abcdef', 2, 3, '12345') AS result;

This query deletes 3 characters starting from the 2nd position in the string 'abcdef' and inserts '12345' at that position. The result will be:

result
--------
a12345f

3. STUFF() with a Column

SELECT product_name, STUFF(product_name, 1, 4, 'New') AS modified_name
FROM products;

This query deletes 4 characters starting from the 1st position in the product_name column and inserts 'New' at that position for each product. The result will show the product_name and the modified version as modified_name.

4. STUFF() with a Variable

DECLARE @myString VARCHAR(50);
SET @myString = 'Data Science';
SELECT STUFF(@myString, 6, 7, 'Analytics') AS result;

This query uses a variable to store a string, deletes 7 characters starting from the 6th position, and inserts 'Analytics' at that position. The result will be:

result
-------------
Data Analytics

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the STUFF() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    description VARCHAR(50)
);

In this example, we create a table named example_table with columns for id and description.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, description) VALUES (1, 'Apple Pie');
INSERT INTO example_table (id, description) VALUES (2, 'Banana Bread');
INSERT INTO example_table (id, description) VALUES (3, 'Cherry Tart');

Here, we insert data into the example_table.

Step 3: Using the STUFF() Function

This step involves using the STUFF() function to modify the description column.

SELECT id, description, STUFF(description, 7, 4, 'Cake') AS modified_description
FROM example_table;

This query retrieves the id, description, and the modified version of the description column for each row in the example_table. The result will be:

id  description   modified_description
--- ------------- ---------------------
1   Apple Pie     Apple Cake
2   Banana Bread  Banana Cakead
3   Cherry Tart   Cherry Cake

Conclusion

The SQL Server STUFF() function is a powerful tool for deleting a specified length of characters from a string and then inserting another set of characters at a specified starting position. Understanding how to use the STUFF() function and its syntax is essential for effective string manipulation and data processing in SQL Server.