Stuff() :
STUFF is used to replace the part of string with some other string ORIt 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 EmployeeDetails1where 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.
Example :
In this example we will replace ?arjun? to ?replace? from table EmployeeDetails1 as shown below
Query : select Id, Replace(Name,?arjun?,?replace?) from EmployeeDetails1where 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 EmployeeDetails1where Id = 2
Output :
Learn SQL Server and revise basics with below video: –
