SQL Server Articles

Submit Article
Home » Articles » SQL Server » String FunctionsRSS Feeds

STUFF Function

Posted By: Sarita Patel     Category: SQL Server     Views: 28001

This article explains STUFF function of sql server with examples.

STUFF function works same as REPLACE function except for searching any value from specified string. It replaces part of the string in a specified string.

Syntax of STUFF Function :

STUFF ( character_expression1 ,start ,length ,character_expression2 )

character_expression1 is a string to be evaluated. It can be a constant, variable, or column of either character or binary data.

start is an integer value that specifies starting index to replace characters from character_expression1. If you specify negative value or greater than the character_expression1 string then function returns null string.

length is an integer value that specifies number of characters to be replaced from character_expression1. If negative value is specified as length, function returns null string.

character_expression2 is a new string to be changed from character_expression1. 

It returns either character data type or binary data type.

Examples of STUFF Function :

Example 1 : Use of STUFF function in select clause

SELECT STUFF('Syntax-Example',1,6,'Language') 


Above example returns new string by replacing 'Syntax' by 'Language' from specified string. 

Example 2 : Use of STUFF function to display field value of table in a select clause

SELECT ContactName, STUFF(ContactName,2,3,'aaan') AS 'New Name'
FROM    Customers

ContactName             New Name
Maria Anders             Maaana Anders
Ana Trujillo                AaaanTrujillo
Antonio Moreno         Aaaannio Moreno
Thomas Hardy           Taaanas Hardy
Christina Berglund      Caaanstina Berglund
Hanna Moos               Haaana Moos

Above example replaces 3 characters starting from index 2 by 'aaan' of all customers name from customers table.


Didn't find what you were looking for? Find more on STUFF Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of STUFF Function is from United States.
View All Articles

Related Articles and Code:

Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

No Comment Found, Be the First to post comment!