Introduction to stuff in SQL Server
Introduction
Hi readers, in this article I will explain what is Stuff function in SQL Server and how does it work.
Stuff function in SQL server replaces a specified length of characters with a new set of characters from a string at a given starting location.
Syntax: STUFF ( original_string , startposition , string_length_to_be_replaced ,replacing_string )
Let’s take an example and understand how stuff works.
In the given example you can see I have declared a variable @Message which contains the original message "I am a good boy" in which I want to replace the word “good” with “bad”.
DECLARE @Message VARCHAR(50),
@StuffInMsg VARCHAR(50)
SET @Message='I am good boy'
SET @StuffInMsg='bad'
Here in the above code you can see, the start position of the word ‘good’ is 6 and its length is 4.
Now in the stuff function we will put
Startposition=6 and
string_length_to_be_replaced=4
So the Stuff function will look like this
SET @Message = STUFF(@Message,6,4,@StuffInMsg)
Now on executing the statement we will see ‘good’ is replace with 'bad' in the given string.
And the final string will be "I am a bad boy"
Output
Enjoy coding.................