SQL STUFF() String Function

The SQL STUFF() function deletes a specified number of characters from a string and replaces them with another substring at a specified starting position.

The STUFF() function is useful for inserting or replacing text within a string, such as modifying part of a field.

In this tutorial, we will go through SQL STUFF() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL STUFF() Function

The basic syntax of the SQL STUFF() function is:

</>
Copy
STUFF(string, start, length, replacement);

Each part of this syntax has a specific purpose:

  • string: The original string where you want to replace characters.
  • start: The starting position (1-based) for the replacement.
  • length: The number of characters to delete from the string.
  • replacement: The substring to insert at the specified position.

The STUFF() function returns the modified string with the specified replacement inserted.

Setup for Examples: Creating the Database and Table

We’ll create a sample products table with fields product_code and product_name. Follow these steps to set up the data for the STUFF() function examples.

1. First, create a new database called inventory:

</>
Copy
CREATE DATABASE inventory;

2. Select the inventory database to work with:

</>
Copy
USE inventory;

3. Create a table named products with the fields product_id, product_code, and product_name:

</>
Copy
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_code VARCHAR(20),
    product_name VARCHAR(100)
);

4. Insert sample data into the products table to use with the STUFF() function examples:

</>
Copy
INSERT INTO products (product_code, product_name)
VALUES
('PRD001', 'Laptop'),
('PRD002', 'Smartphone'),
('PRD003', 'Tablet'),
('PRD004', 'Monitor'),
('PRD005', 'Keyboard');

With this setup complete, you can run the STUFF() function examples to test and view results in the products table.

Examples: Using STUFF() in SQL Queries

We’ll go through examples demonstrating the STUFF() function in SQL, using sample data from a products table with fields product_code and product_name.

1. Replacing Part of a Product Code

To replace characters in the product_code column starting at the third character, with a length of 2, and replace them with “XX”:

</>
Copy
SELECT product_code, 
       STUFF(product_code, 3, 2, 'XX') AS modified_code
FROM products;

This query replaces two characters starting at the third position of product_code with “XX”.

2. Inserting Text into a Product Name

To insert the word “New” at the fifth character position of each product_name:

</>
Copy
SELECT product_name, 
       STUFF(product_name, 5, 0, 'New ') AS updated_name
FROM products;

This query inserts “New ” at the fifth position in each product_name without deleting any existing characters (length of 0).

3. Replacing Multiple Characters in a String

To replace the first three characters of each product_code with “ABC”:

</>
Copy
SELECT product_code, 
       STUFF(product_code, 1, 3, 'ABC') AS new_code
FROM products;

This query replaces the first three characters of product_code with “ABC”.

4. Using STUFF() with Concatenation

To modify product_name by replacing part of the text and appending additional information:

</>
Copy
SELECT product_name, 
       CONCAT(STUFF(product_name, 3, 2, '***'), ' - Updated') AS modified_name
FROM products;

This query replaces two characters in product_name starting at the third position with “***” and adds ” – Updated” at the end.

FAQs for SQL STUFF()

1. What does the SQL STUFF() function do?

The STUFF() function removes a specified number of characters from a string and inserts another string at a specified position.

2. Can STUFF() handle NULL values?

If any argument in STUFF() is NULL, the result is NULL.

3. Is STUFF() supported in all SQL databases?

No, STUFF() is specific to SQL Server. Other databases may have similar functions, such as INSERT() in MySQL.

4. Can STUFF() be used to insert without replacing?

Yes, set the length argument to 0 to insert the replacement string without deleting any characters.

5. What happens if the start position is out of range?

If start is greater than the string length, STUFF() returns NULL.