Stuff in SQL Server

Stuff function in SQL is used to insert one string into the another string. It may or may not result in replacing the characters of the existing string, based on the parameters provided to the function. It’s syntax is

Stuff (existing_string, start_point, length, string_to_insert)


  • existing_string is the string in which we would like to insert our string. This could be column name of any table also.
  • start_point is the position from where we would like to start inserting our string
  • length is the number of characters which we like to replace in the existing string. If 0 is specified, then the new string will be added to the existing string and no character in existing string will be replaced.
  • string_to_insert is the new string we would like to insert in the existing string.

Let’s use a simple example to discuss this concept now. See the commands below :

Stuff in SQL Server

We have declared a simple string and applied STUFF function with two different arguments. In first, we have specified the 9th position as the start_point and 2 as the length of the characters to be replaced in the existing_string. So it updates the existing string accordingly. In second case, we have specified the length as 0. So it does not replace any character in the existing_string and just fits the new string at the specified position i.e. 9th position.

Similarly we could have used any table column in the Select statement above.

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server and tagged . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s