Stuff Vs. Replace Vs. Substring

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

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.