Stuff and Replace in SQL Server 2012

Here, we will have a look at how to use the Stuff and Replace functions in SQL Server 2012.

Here, we will have a look at how to use the Stuff and Replace functions in SQL Server 2012. Both Stuff and Replace are used to replace characters in a string. So let's have a look at a practical example of how to use the Stuff and Replace functions in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
 

Stuff Function in SQL Server

 
The Stuff function is used to replace characters in a string. This function can be used to delete a certain length of the string and replace it with a new string.
 
Syntax
 
STUFF (String, Startingposition, LengthofReplaceChar, ReplaceString)
 
Here,
 
String: String to be overwritten
Startingposition: Starting Position for overwriting
LengthofReplaceChar: Length of replacement string
ReplaceString: This expression will replace length characters of String beginning at start.
 
Example
 
The Stuff function looks like this:
  1. Declare @fname Varchar(100)  
  2. Declare @lname Varchar(100)  
  3. declare @result varchar(50)  
  4. SET @fname = 'rohatash'  
  5. SET @lname = 'kumar '  
  6. SET @result  =  STUFF(@fname, 3,7, @lname )  
  7. print 'Result: ' + @result 
OUTPUT
 
Stuff Function in SQL Server 
 
Example
 
The Stuff function only replaces the string it finds at the starting location we specify for the number of chars we want it to replace, as in:
  1. select stuff('Hello world',7, 5,'Rohatash'as Stufffunction  
Result
 
stuff-Function-in-sqlserver.jpg 
 

Replace function in SQL Server

 
Replace all occurrences of the second given string expression in the first string expression with a third expression.
 
Syntax
 
REPLACE (String, StringToReplace, StringTobeReplaced)
 
Here,
 
String - Input String
StringToReplace - The portion of string to replace
StringTobeReplaced - String to overwrite
 
Example
 
The Replace function looks like this:
  1. Declare @fname Varchar(100)  
  2. Declare @lname Varchar(100)  
  3. declare @result varchar(50)  
  4. declare @replacestring varchar(30)  
  5. SET @fname = 'rohatash'  
  6. SET @lname = 'ro'  
  7. set @replacestring ='ku'  
  8. SET @result  =  replace(@fname, @lname ,@replacestring)  
  9. Print 'Result: ' + @result  
OUTPUT
 
Replace function in SQL Server 
 
Example
  1. select replace('Rohatash kumar','ha','c')  
Result
 
Replace function in SQL Server 
 

Difference Between Stuff and Replace Functions

 
If we only wanted to replace the first occurrence then Replace wouldn't work, since it always replaces ALL occurrences of the string. But Stuff would, since it only replaces the string it finds at the starting location we tell it for the number of chars we want it to replace. Or we can say that:
  1. The STUFF function is used to overwrite the characters of the string.
  2. The Replace function is used to replace all occurrences of a particular string with the specified string.
The STUFF function is used to overwrite the characters of a string.
 
SELECT STUFF('Rohatash', 3, 3, 'ABC')
 
Output
 
RoABCash

The Replace function is used to replace all occurrences of the second given string.
 
SELECT REPLACE('Rohatash', 'a', 'M')
 
Output
 
RohMtMsh