STUFF Function In SQL Server

STUFF function is used to insert a string into another string. Basically, it deletes the characters from a source string and inserts another string at the specified position.
 
 
 
Syntax
 
Here, character_exp is an expression of the character data to be modified. Start is an integer, which specifies start position in character_exp to delete and insert another string (i.e. replacewith_exp) from here and length is an integer, which specifies the number of characters to be deleted. Replacewith_exp is a character expression to be inserted at the start position.
 
Example 1
  1. Select STUFF('www.google.com',5,6,'c-sharpcorner')  
 
In the example, mentioned above, we have specified the location to delete(i.e. 5) and delete 6 characters from the start position and add another string in this place.
 
Example 2

 

In the second example, we have inserted one string between another string.
 
Example 3

Masking Credit Card Number using STUFF function
 

In the example, mentioned above, we have masked all the numbers except the last four of a credit card number.
 
Example 4

STUFF function returning Null Value

If start position is 0, a null value is returned. If start position is greater than the length of initial character expression, the null value is returned. If length(integer parameter) of the string to delete is negative, a null value is returned.
 
The other article related to SQL can be accessed at Copy Table Schema and Data From One Database to Another Database in SQL Server