STUFF function in SQL Server

This function is used to replace new string from the start position of Given string and replace the Number of character in Given string.

Example: Select Stuff ('Pritam', 3, 3, '***')

Here we have given a string "Pritam" and want to replace "***". In middle of this 2 string we have used 3,3.

The first 3 will count the character position of "Pritam". Then It will replace the next 3 character from position 3 and insert "***".(Each * for each position)

Now we will do more research on Stuff function.

As Microsoft mentioned:

If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.

An error is raised if the resulting value is larger than the maximum supported by the return type.

Select Stuff ('Pritam', 0, 3, '***') ---> Output: NULL

Select Stuff ('Pritam',-1, 3, '***') ---> Output: NULL

Select Stuff ('Pritam',6, 6, '***') ---> Output: Prita***

Select Stuff ('Pritam',1, 10, '***') ---> Output: ***

Select Stuff ('Pritam', 6,3, '******************') 

Output: Prita******************