SQL Server STUFF Function

In this post , I will explain the STUFF function of SQL Server of string function category.
In my previous posts, I explained UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server.

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 start is 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 in character_expression1.

Return Type- Returns character data.

Example-
 

Declare @nstring varchar(50)

set @nstring='Manish Dubey'

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

--Ouput M.Dubey

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