STUFF Function in SQL Server

Stuff function is used to insert a string into another string. Basically, it deletes characters from a source string and inserts another string at specified position.

Syntax:

Here,
Character_exp is an expression of character data to be modified.
Start is an integer that specifies start position in character_exp to delete and insert another string (i.e. replacewith_exp) from here.
length is an integer, specifies the no. of characters to delete.
Replacewith_exp is character expression to be inserted at the start position.

Example 1:

Select STUFF('www.google.com',5,6,'c-sharpcorner')

In above example, 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 in another string.

Example 3: Masking Credit Card Number using STUFF function 

In above example, We have masked all numbers except last four of a Credit card number.

Example: 4 STUFF function returning Null Value
If start position is 0 then a null value is returned. If Start position is greater than the length of initial character expression then again null value is returned. If length(integer parameter) of string to delete is negative then a null value is returned. 

Other articles related to SQL:

• Copy Table Schema and Data From One Database to Another Database in SQL Server