SQL Server STUFF Function

Here, I am going to explain STUFF function of SQL Server.

STUFF() Function

This function is used to delete the specified length of characters from input string and insert a another set of characters from specified starting point in input string
  

Syntax- STUFF(character_expression1, start, length, character_expression2)
Arguments-

• character_expression1 : An expression of character data.
• start : An int value, which specified the starting point in character_expression1. If startis longer than length of character_expression1 then a Null string is returned
• length : An int value, which specified the length of characters to delete. If length is longer than length of character_expression1 then deletion occurs up to the last character in the last character_expression2
• character_expression2 : An expression of character data, which will be insert incharacter_expression1.

Return Type- Returns character data.

Example-
  

Declare @nstring varchar(50)

set @nstring='Nayab Shaik'

select Stuff(@nstring, 2,5,'.')

--Ouput N.Shaik

Note- If start or length is negative then a Null string is returned.