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.
STUFF ( string1 , start , length , string2)
- 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
- String1 and String2 can be a character or binary data
- String2 is the character data that will be inserting in to string1
This function returns two different data type based on the argument type.
- It returns character data if string1 and string2 are the supported character data types.
- 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
- If the start position or the length is negative.
- If the starting position is larger than length of the string1.
- If the start position is 0 (Zero)
- 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.
SELECT STUFF(‘This is a apple’, 9, 2, ‘an ‘ )
This is an apple
SELECT STUFF(‘This is a apple’, 0, 0, ‘an ‘ )
SELECT STUFF(cast(‘This is a apple’ as varchar(8001)), 9,2, ‘an ‘ )
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 can write their experience of using REPLACE and STUFF function in the comment section.
Thanks for Reading.