Stuff, Replace and Substring in SQL Server

Stuff, Replace and Substring in SQL Server

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 :

Image for post

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 :

Image for post

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 :

Image for post

Learn SQL Server and revise basics with below video: –

No Responses

Write a response