In this article, we will learn how to use the STUFF function in SQL Server. We will see how it works, what its parameters are, and we will also build some examples to familiarize ourselves with its use.
STUFF function in SQL Server
The STUFF function is a bit peculiar. Why do we say this? Because it has a double purpose. On the one hand, it removes certain characters from a string, and on the other, it adds characters to that same string. That is, it has a double function, delete and add characters to a string.
STUFF function syntax
For a better understanding of how this particular function works, we will study its syntax.
STUFF(original_string, start_position, length, characters_to_add)
Script 1. STUFF function syntax
As we can see, the STUFF function receives 4 parameters:
|Parameter name||Type||Brief description|
|original_string||String||This is the original string to which we will add and remove the characters.|
|start_position||Integer||This parameter indicates the position in which the characters will be removed and added.|
|length||Integer||This parameter is only useful for the deleting part of the function. Since its purpose is to indicate how many characters should be removed.|
|characters_to_add||String||This last parameter is only useful for the “adding part” of the function. Since it indicates the characters to be added.|
Table 1. SQL Server STUFF function parameters
- The value of the start_position parameter must be between 1 and the length of the original string. If we assign a value outside that range, the function will return NULL
- The value of the length parameter must be between 0 and the length of the original string. If we assign a value outside that range, the function will return NULL
We are going to build some queries to familiarize ourselves with the use of the function.
In our first example, we will use the original string: “I want to replace these characters: XYZ with these: ABC”
And we will use the STUFF function to remove the XYZ characters and add the ABC characters instead.
SELECT STUFF('I want to replace these characters: XYZ with these: ABC', 37, 3, 'ABC') AS 'STUFF result';
Script 2. SQL STUFF example 1
Table 2. Results of Example 1
The STUFF function is also flexible or versatile. What does it mean? Well, for example, we can delete some characters without having to add others. Let’s build a query to illustrate it.
SELECT STUFF('I want to delete these characters: XYZ without adding others.', 36, 3, '') AS 'STUFF result';
Script 3. SQL STUFF example 2
Table 3. Results of Example 2
Continuing with the versatility of the function, we can also add some characters without having to delete anything. Let’s build a query to see how we can do that.
SELECT STUFF('I want to add these characters: XYZ without deleting others: ', 61, 0, 'XYZ') AS 'STUFF result';
Script 4. SQL STUFF example 3
Table 4. Results of Example 3
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.