intro
Learn everything you need to know about inserting a string into another string using the SQL TRIM function in SQL Server.
Efficient string handling is a crucial aspect of data processing in a database. That is why databases such as SQL server are equipped with numerous functions for string manipulation. Among them, the SQL STUFF function stands out as a powerful tool for inserting one string into another at a specific location.
In this article, you will learn what the STUFF SQL Server function is, how it works, and how to use it in real-world scenarios.
Let’s dive in!
What Is the STUFF Function in SQL Server?
The SQL Server STUFF function inserts a string into another string. Specifically, it removes a given length of characters in the original string from a given position and then inserts a second string into the first string from that initial position.
Note that the SQL STUFF function is exclusive to Transact-SQL. While other databases like MySQL and PostgreSQL do not offer a direct replacement, you can achieve the same result by concatenating string manipulation functions.
STUFF SQL Server: Syntax and First Examples
This is what the syntax of the STUFF SQL Server function looks like:
1
STUFF(original_string, start, length, replace_string)
Where:
The SQL STUFF Function in Action
You’re ready to see the SQL STUFF function in action with a simple example. This SQL query:
1
SELECT STUFF('Hello###World!', 6, 3, ', ');
Will return:
1
Hello, World!
In particular, what the STUFF SQL Server function does in the above query is:
If replace_string is NULL, the STUFF SQL Server function will just remove some characters from original_string:
1
SELECT STUFF('Hello###World!', 6, 3, NULL);
This time, the result is:
1
HelloWorld!
Keep in mind that STUFF can also return NULL. Here are all the scenarios where the result is NULL:
Note that if start + length - 1 is greater than or equal to the number of characters in original_string, deletion occurs up to the last character of original_string:
1
SELECT STUFF('Hello###World!', 6, 15, 'Everyone!');
The result will be:
1
Hello, Everyone!
Instead, if the length is zero, the insertion occurs at the start location and no characters are deleted:
1
SELECT STUFF('Hello###World!', 6, 0, '@@@');
This time, the output will be:
1
Hello@@@###World!
Warning: If the STUFF resulting value is larger than the maximum supported by the return type, an error is raised.
Use Cases of STUFF in SQL Server
See the SQL STUFF function in action in two real-world examples on the following Employee table:
The queries below will be executed in DbVisualizer, the database client with the highest user satisfaction in the market. You can run them in any other SQL client.
Concatenating Values With Delimiters
The goal here is to concatenate the surname values from multiple rows into a single string with a delimiter between them.
To achieve that, you may be thinking of using a FOR XML PATH SQL Server expression:
1
SELECT ', ' + Surname FROM Employee FOR XML PATH('');
The result of that query is close to the desired outcome:
1
, Johnson, Brown, Martinez, Miller, Smith, Williams, Davis, Anderson, Lee, Turner, Garcia, Lopez
However, that trailing , string is quite annoying. Remove that with STUFF:
1
SELECT STUFF((SELECT ', ' + Name FROM Employee FOR XML PATH('')), 1, 2, '');
The new result will be:
1
Johnson, Brown, Martinez, Miller, Smith, Williams, Davis, Anderson, Lee, Turner, Garcia, Lopez
Great! Mission complete.
This is just a simple SQL Server STUFF FOR XML PATH example, but can achieve more complex results by combining those two operators.
Mask Sensitive Data
Suppose you want to get your employees' e-mails redacted to avoid exposing sensitive data. Here is how you can achieve this using STUFF in SQL Server:
1
SELECT Id, STUFF(Email, 2, GREATEST(CHARINDEX('@', Email) - 3, 0), '*') AS MaskedEmail
2
FROM Employee;
Let’s break down the length argument of the SQL STUFF call. CHARINDEX('@', Email) returns the index of the @ character in the Email column. To this is subtracted 3 so point to the second-to-last character before @. Since you do not want length to be negative, you must wrap it with GREATEST and assume 0 as the minimum value.
Now, the substring to replace with * goes from the second character in the email string to the second-to-last. The result will be:
Et voilà! Masked emails are displayed as required.
SQL STUFF Function: Best Practices
Take a look at the list of the best practices of the STUFF function in SQL Server:
Conclusion
In this guide, you understood what the SQL STUFF function is and how it works. You now know that STUFF is a proprietary SQL Server function to insert a substring into another string. Thanks to the examples shown here, you have also learned when to use it in real-world scenarios.
Using the STUFF function in SQL Server becomes easier with a powerful database client. DbVisualizer is a full-featured database client that supports several DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free!
FAQ
What databases support the SQL STUFF function?
The SQL STUFF function is primarily supported in the Microsoft SQL Server database. Some other database systems might offer similar functionality, but STUFF is a non-standard function. So, it is not available across all SQL database platforms.
How to use SQL STUFF with integer data?
The STUFF function in SQL Server also works with integer data. For example, you can use it to replace the second digit of a number with 3 as follows:
1
SELECT STUFF(123456, 2, 1, 3);
The result will be:
1
133456
Why is the SQL Server STUFF FOR XML PATH usage popular?
The SQL Server STUFF function works well with FOR XML PATH because it allows you to remove the trailing characters added when concatenating rows into a single string. FOR XML efficiently handles string aggregation tasks and STUFF refines the result as desired.
Is the STUFF function in SQL Server efficient?
Yes, the STUFF function in SQL Server is efficient for string manipulation tasks, especially when used in conjunction with other operators like FOR XML PATH. However, its efficiency can vary depending on factors such as the size of the dataset and the complexity of the operation.
How do NULL values affect STUFF SQL Server results?
If the original string is NULL, the result of the STUFF SQL Server function will be NULL. If the substitution string is NULL, STUFF will not replace any characters and will just delete them.
