Stuff in SQL Server

Stuff function in SQL is used to insert one string into the another string. It may or may not result in replacing the characters of the existing string, based on the parameters provided to the function. It's syntax is:

Stuff (existing_string, start_point, length, string_to_insert)

Here,

existing_string is the string in which we would like to insert our string. This could be column name of any table also.
start_point is the position from where we would like to start inserting our string
length is the number of characters which we like to replace in the existing string. If 0 is specified, then the new string will be added to the existing string and no character in existing string will be replaced.
string_to_insert is the new string we would like to insert in the existing string.

Let's use a simple example to discuss this concept now. See the commands below :

We have declared a simple string and applied STUFF function with two different arguments. In first, we have specified the 9th position as the start_point and 2 as the length of the characters to be replaced in the existing_string. So it updates the existing string accordingly. In second case, we have specified the length as 0. So it does not replace any character in the existing_string and just fits the new string at the specified position i.e. 9th position. Similarly we could have used any table column in the Select statement above.