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     2322
  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 .Net developer having 4+ years of experience. He loves writting technical article for .Net developers. Najmul Hoda
No Photo
 
Country India
Company BirlaSoft
Home Page www.dotnetlogix.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   
  
    
 
Section sponsored by