Differences between STUFF, REPLACE and SUBSTRING in SQL Server

The differences between STUFF, REPLACE and SUBSTRING functions is one of the most common interview question. By using the STUFF, REPLACE and SUBSTRING functions we can modify the strings as per our requirement.

STUFF():
STUFF is used to replace the part of string with some other string OR It delete a specified length of characters within a string and replace with another set of characters. 

Syntax:

STUFF(string_expression , start, length, string_expression2)

string_expression : represents the string in which the stuff is to be applied. 
start : indicates the starting position of the character in string_expression.
length : indicates the length of characters which need to be replaced. 
string_expression2 : indicates the string that will be replaced to the start position.

Example:

1. DECLARE @str VARCHAR(35) = 'ABCDEFGH'    
2. SELECT @str, STUFF(@str,4,5,'_STUFF')    

Output: By observing the output it replace the index position 4 from next 5 characters with '_STUFF' in a give string_expression.

 

REPLACE():
REPLACE is used to replace all the occurrences of the given pattern in a string.

Syntax

REPLACE (string_expression, replace, string_expression2)

string_expression : Specifies the string that contains the substring to replace all instances of with another.
Replace : Specifies the substring to locate.
string_expression2 : Specifies the substring with which to replace the located substring.

Example: 

1. DECLARE @str VARCHAR(35) = 'ABCDEFGH'    
2. SELECT @str, REPLACE(@str,'DEFGH','_REPLACE')  

Output: By observing the output 'DEFGH' is replace with '_REPLACE' in 'ABCDEFGH'.



SUBSTRING(): 
SUBSTRING returns the part of the string from a given string_expression. 

Syntax

SUBSTRING (string_expression, start, length)

Example:

1. DECLARE @str VARCHAR(35) = 'ABCDEFGH'    
2. SELECT @str, SUBSTRING(@str,1,3)  

Output: By observing the output it returns specified string from a given string_expression.



Summary
Lets take an example to describe STUFF, REPLACE and SUBSTRING functions.

Example:

1. DECLARE @str VARCHAR(35) = 'ABCDEFGH'    
2. SELECT @str, STUFF(@str,4,5,'_STUFF')    
3. SELECT @str, REPLACE(@str,'DEFGH','_REPLACE')    
4. SELECT @str, SUBSTRING(@str,1,3)  

Output: Here you can observe the clear differences between STUFF, REPLACE and SUBSTRING.