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

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

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

 

 

 

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