Home > SQL Server, T-SQL > Delete and Insert a string into another string using STUFF function

Delete and Insert a string into another string using STUFF function


STUFF() T-SQL string function is used to insert a string into another string by removing a part of string from it. The starting position and the length given in the argument is the location and length of the string to be removed from the string1 and these two numeric value must be within valid range.

Syntax

STUFF ( string1 , start , length , string2)

Arguments

  • String1 is the character data in which we insert the string2.
  • String2 is the character data that we wish to insert into string1.
  • Start is an integer or big integer value. It is to specify the starting position in the string1
  • Length is an integer or big integer value. It is to specify the length of the character(s) that are to be removed from the string1

Note:

  • String1 and String2 can be a character or binary data
  • String2 is the character data that will be inserting in to string1

Return value

This function returns two different data type based on the argument type.

  1. It returns character data if string1 and string2 are the supported character data types.
  2. It returns binary data if string1 and string2 are the supported binary data types.

Points to remember

This function returns NULL when any one of the following statement is true

  1. If the start position or the length is negative.
  2. If the starting position is larger than length of the string1.
  3. If the start position is 0 (Zero)
  4. If the length to delete is longer than the first string.

Otherwise, An error is raised if the resulting value is larger than the maximum supported by the return type. Maximum length of the data is 8000 characters.

Example 1:

SELECT STUFF(‘This is a apple’, 9, 2, ‘an ‘ )

Output

This is an apple

Example 2:

SELECT STUFF(‘This is a apple’, 0, 0, ‘an ‘ )

Output

NULL

Example 3:

SELECT STUFF(cast(‘This is a apple’ as varchar(8001)), 9,2,  ‘an ‘ )

Output

The size (8001) given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000).

This function is a useful string function to replace a string with another string without searching. We can also use REPLACE function to do the same task. But, replace function finds and replaces the given string. If the search string is not found then the replace function will not affect the source string. Learn more about REPLACE().

Readers thought

Readers can write their experience of using REPLACE and STUFF function in the comment section.

Thanks for Reading.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: