Stuff() function – Transact SQL

STUFF() Function – Transact SQL
The STUFF function deletes a specified length of characters and inserts a designated string at the specified starting point. Important note: STUFF function has a limitation if 4000 characters.

The syntax is as follows:
STUFF ( character_expression, start, length, character_expression )
character_expression: argument of this function is the character expression to be modified.
start: starting position of the string to be inserted
length: number of characters to delete within the string in the first argument
character_expression: the actual character expression that you want to insert

Example:
SELECT STUFF ( 'My Name is X. I am an experienced SQL Developer.', 12, 1, 'Hussain Patel' );
GO
Output: My Name is Hussain Patel. I am an experienced SQL Developer.

In the above example, we are passing 1, as length parameter (3rd parameter), this means only one character at position 12 will be deleted and replaced by the last character expression (4th parameter – Nasir Gondal). if the length parameter is passed as 0, then the STUFF function simply inserts the second string into the first string at the position 12 deleting zero characters so the last character expression will be inserted before X (see below).

Example:
SELECT STUFF ( 'My Name is X. I am an experienced SQL Developer.', 12, 0, 'Hussain Patel' );
GO
Output: My Name is Hussain PatelX. I am an experienced SQL Developer.

Q. What is the output of the following:
SELECT STUFF ( 'My Name is X. I am an experienced SQL Developer.', 12, 5, '' );
GO

A: My Name is am an experienced SQL Developer.

Hope this helps.
Hussain Patel