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)

  1. declare @articletitle varchar(50)  
  2. set @articletitle='this is my first article on sql'  
  3.   
  4. select @articletitle as Title  
  5. select STUFF(@articletitle,2,8,'abcd'as ChangedTitle  
query of Stuff function

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
  1. declare @articletitle varchar(50)  
  2. set @articletitle='this is my first article on sql'  
  3.   
  4. select @articletitle as Title  
  5. select Replace(@articletitle,'i','abcd'as ChangedTitle  
replace function

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
  1. declare @articletitle varchar(50)  
  2. set @articletitle='this is my first article on sql'  
  3.   
  4. select @articletitle as Title  
  5. select substring(@articletitle,4,5) as ChangedTitle  
substring function in sql

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.