User Id :    Password :      New Member   Forgot Password  
 
Introduction to stuff in SQL Server
Description 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.   No. of Views     2831
  Rating     4
Author Najmul Hoda   Posted On     29 Jul 2011
Tags SQL SERVER,General    

Sample Code   Download Code

 

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.................

 

 

About Author

About Author He is a team lead, having 8+ years of experience. He loves writing technical article for .Net developers. Najmul Hoda
No Photo
 
Country United States
Company BirlaSoft
Home Page http://www.magixchat.com

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

 
 
Posted By Annonymous on 11 Aug 2011 at 10:38 AM
 
Very nice enlightenment of stuff function.
 
 
 
Posted By Annonymous on 11 Aug 2011 at 10:36 AM
 
Very nice explanation of stuff function.
 
 
 
Posted By Gaurav on 03 Aug 2011 at 04:25 AM
 
Hi Najmul Good explanation. Thanks
 
 
   
Write your comment here.
Comment
Verification Code