Stuff, Replace and Substring in SQL Server
Stuff() :
STUFF is used to replace the part of string with some other string
OR
It deletes a specified length of characters within a string and replace with another set of characters.
Syntax :
STUFF(string/column_expression, start, length, string_expression2)
string/column_expression : represents the string/column in which the stuff is to be applied.
start : indicates the starting position of the character in string/column_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 :
In this example we will replace the 2nd index position of ‘arjun’ to 4 characters with ‘stuff’ from table EmployeeDetails1 as shown below
Query :
select Id, Stuff(Name,2,4, ‘stuff’) from EmployeeDetails1
where Id = 2
Output :
Replace() :
Replace is used to replace all the characters from the given pattern in a string.
Syntax :
REPLACE(string/column_expression, replace, string_expression2)
string/column_expression : specifies the string/column that contains characters need to be replaced with another.
replace : specifies the string need to be replaced.
string_expression2 : specifies the string with which to be replaced.
Get Arjun Sharma’s stories in your inbox
Join Medium for free to get updates from this writer.
Example :
In this example we will replace ‘arjun’ to ‘replace’ from table EmployeeDetails1 as shown below
Query :
select Id, Replace(Name,’arjun’,’replace’) from EmployeeDetails1
where Id = 2
Output :
Substring() :
substring returns the part of the string of characters from a string/column.
Syntax :
SUBSTRING(string/column_expression, start, length)
string/column_expression : represents the string/column in which substring needs to be applied.
start : indicates the starting position of the character in string/column_expression.
length : indicates the length of characters till where we needed.
Example :
In this example by using SUBSTRING() we will get the string ‘arj’ from string ‘arjun’ as shown below
Query:
select Id, substring(Name,1,3) from EmployeeDetails1
where Id = 2
Output :
Learn SQL Server and revise basics with below video: -
