Stuff Vs. Replace Vs. Substring

Stuff:

Using the stuff function we can remove a substring of a certain length of a string and replace it with a new string.
Refer the following arguments that we need to pass to the Stuff function:

Stuff, Replace and Substring functions in SQL Server

First argument passed to the function is the expression. Second argument is the starting length from where the new characters are to be added. Third argument tells how many characters to allocate to the new string being embedded. Fourth argument takes the new string to be embedded as a parameter.

1 2 3

declare @text varchar(50)   set @text='Welcome to .Net Snippets'   select @text as Title

Let us run this and check the output.

Stuff, Replace and Substring functions in SQL Server

We will now add some characters to this string. See the following Stuff function.

1	select STUFF(@text,24,3,'!!') as Title

It will insert “!!” at the end of the expression. Let us check the output.

1 2 3

declare @text varchar(50)   set @text='Welcome to .Net Snippets'   select STUFF(@text,24,3,'!!') as Title

Stuff, Replace and Substring functions in SQL Server

Replace:

As the function name replace indicates, the replace function replaces all occurrences of a specific string value with another string.

Refer the following arguments that we need to pass to the Replace function:

Stuff, Replace and Substring functions in SQL Server

The replace function takes the expression as the first parameter. Second parameter is the string present in the expression that is to be replaced. The third parameter is the string that will replace the second parameter in the function.

1 2 3

declare @text varchar(50)   set @text='Welcome to .Net Snippets'   select @text as Title

Stuff, Replace and Substring functions in SQL Server

We will replace few characters in this expression using Replace function.

1	select Replace(@text,'.','Dot') as Title

Stuff, Replace and Substring functions in SQL Server

Substring:

Using Substring function we get a specific portion of the expression.

Refer the following arguments that we need to pass to the Stuff function:

Stuff, Replace and Substring functions in SQL Server

The first parameter Substring function takes is the expression. Second parameter is the starting position of the expression which takes integer as input and the third parameter is the length which means the characters till this length from the starting position of the second parameter will be returned from the expression.

Let us execute the following commands.

1 2 3

declare @text varchar(50)   set @text='Welcome to .Net Snippets'   select SUBSTRING(@text,11,14) as Text

Stuff, Replace and Substring in SQL Server

We get the preceding result using Substring function.