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')
Output
Language-Example
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
Output
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.