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.


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.

Create A Table

Step 2: Insert Data in EmpMst.

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

Insert Data in EmpMst

Step 3: See Table Record.

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,

Replace String

2. Using Nested Stuff

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

Using Nested Stuff
Note: To See Code Please Find out attachment.

If you have any questions, then please mention in the comments section. 

Similar Articles