Stuff Vs. Replace Vs. Substring

In this article we learn how and why we use the stuff, replace and Substring functions in SQL Server.

STUFF: Using the stuff function we delete a substring of a certain length of a string and replace it with a new string.

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

SUBSTRING: Using the substring function we get a portion of a string.

STUFF function syntax

STUFF(mainString, whereToStart, stringLenght, replaceString)

declare @articletitle varchar(50)
set @articletitle='this is my first article on sql'

select @articletitle as Title
select STUFF(@articletitle,2,8,'abcd') as ChangedTitle

Note: The Stuff function executes only one time. It works on the string position, like at which position you want to execute the stuff function.

Replace function syntax

declare @articletitle varchar(50)
set @articletitle='this is my first article on sql'

select @articletitle as Title
select Replace(@articletitle,'i','abcd') as ChangedTitle

Note: The Replace function works on a specific char or string. As in the above example the variable title has an “i” 4 times. When we execute the replace function it replaces all “i” with “abcd”.

Substring function syntax

declare @articletitle varchar(50)
set @articletitle='this is my first article on sql'

select @articletitle as Title
select substring(@articletitle,4,5) as ChangedTitle

Note: In the example above we see that the substring function returns only the portion of the string that we are passing as a parameter. In the above example the variable articletitle length is 31. At position 4 the variable articletitle has “s” and after we count 5 more char, so we got the output.