STUFF() vs REPLACE() string functions in SQL Server

There are quite few useful functions in SQL server to manipulate strings. In this article, we take a look at two functions which can be used for replacing a string within a string.

1. STUFF()

This function can be used for delete a certain length of the string and insert a new string in the deleted place.

Syntax

Code:

STUFF ( InputString, start, length, ReplacedString )

Example

Code:

Select STUFF ("this is a test", 7, 2, "was") -- Result - this was a test

2. REPLACE()

This function replaces all the occurrences of a string expression with a new string within an input string.

Syntax:

Code:

REPLACE ( InputString , StringToReplace , NewString )

Example,

Code:

Select REPLACE ("This is a test and it is successful!�,"is","was") -- Result - This was a test and it was successful!

I frequently use REPLACE function but never come across a scenario inserting a specific portion of text in a string after deleting the portion of it. The thing about STUFF() is we need to know the starting position where it needs to insert. Probably we may need to use the functions like CHARINDEX() to find the position of the string before we can insert a new string.