Difference between Replace, Stuff and substring in Sql Server

In this post we are going to see what is the difference between Replace, Stuff and Substring in SQL SERVER.

This three functions are used to manipulate the strings in different ways.Let we see the operations of this three functions now.

REPLACE
***************
replace is used to replace a certain string with specified string.

REPLACE(expression, goingtoreplace string, replace expression)

Example:
declare @string varchar(30) = 'SampleData'

select REPLACE(@string,'a','AB');

Here you can see that in the string SampleData we are going to replace the occurrences of 'a' with 'AB'

output: SABmpleDABtAB


STUFF
*********
Stuff is used to replace the certain string in a given string which replaces the given length of string with start position

STUFF(expression, start index, no of chars, replacing expression)

Example:
declare @string varchar(30) = 'SampleData'

select STUFF(@string,3,4,'ABCDEF')

Here you can see that in the string SampleData we are going to replace the certain string with given no of chars with 'ABCDEF'

output: SaABCDEFData

SUBSTRING
*************
substring is used to get the part of the string from the given string.

SUBSTRING(expression, start index, no of chars)

Example:
declare @string varchar(30) = 'SampleData'

select SUBSTRING(@string,7,4)

output: Data

From this post we can learn the difference between Replace, Stuff and Substring in SQL Server.