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.