Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to use SQL Server STUFF function – Querychat

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

Important considerations:

  • 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

Examples

We are going to build some queries to familiarize ourselves with the use of the function.

Example 1

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

Result:

STUFF result
I want to replace these characters: ABC with these: ABC

Table 2. Results of Example 1

Example 2

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

Result:

STUFF result
I want to delete these characters:  without adding others.

Table 3. Results of Example 2

Example 3

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

Result:

STUFF result
I want to add these characters: XYZ without deleting others:XYZ

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.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc