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
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:
Declare @fname Varchar(100)
Declare @lname Varchar(100)
declare @result varchar(50)
SET @fname = 'rohatash'
SET @lname = 'kumar '
SET @result = STUFF(@fname, 3,7, @lname )
print 'Result: ' + @result
OUTPUT
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:
select stuff('Hello world',7, 5,'Rohatash') as Stufffunction
Result
Replace function
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:
Declare @fname Varchar(100)
Declare @lname Varchar(100)
declare @result varchar(50)
declare @replacestring varchar(30)
SET @fname = 'rohatash'
SET @lname = 'ro'
set @replacestring ='ku'
SET @result = replace(@fname, @lname ,@replacestring)
Print 'Result: ' + @result
OUTPUT
Example
select replace('Rohatash kumar','ha','c')
Result
The Difference Between the 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:
- The STUFF function is used to overwrite the characters of the string.
- 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