Use Stuff Function In SQL Server

What is Stuff?

The Stuff string function deletes a sequence of characters from a source string and then inserts a string into another string.

Syntax

Stuff (Source_string, start, length, replace string)

Parameters Arguments

1. Source_String:
   
   Source_String is characters data. It can be s constant, variable, column or either characters or binary data.
   
   
2. Start:
   
   It specifies location to start deleting and inserting string.
   
   
3. Length:
   
   It specifies the number of characters that can be deleted in source string.
   
   
4. Replace:
   
   It can replace characters of the start and length of that character.

Different ways to use stuff function:

Step 1: Create a Table.

Table Column Value Comma Separated. Firstly, create an EmpMst Table.



Step 2: Insert Data in EmpMst.

You can insert data in EmpMst Table. Insert 5 records in table.



Step 3: See Table Record.



Step 4: Using Stuff in Table Column Data.

Comma separated value EmpName.



1. Replace String

Replace String in given String. e.g “John Smith” is a string to replace string “sm Bon”.
Start Position: 2 and Length: 4,



2. Using Nested Stuff

Using Stuff Inside another stuff. Given date string: 07012016 to string: 07/01/2016.


Note: To See Code Please Find out attachment.