SQL STUFF

SQL STUFF function is used to delete a sequence of given length of characters from a string and inserting the given sequence of characters from the specified starting index.

SYNTAX

STUFF(string, start, length, new_string)

string – It is the string to be modified.

start  – The starting index from where the given length of characters will be deleted and new sequence of characters will be inserted.

length -The number of characters to delete from string. STUFF function will not not remove any characters from the string.

new_string -The new set of characters (string) to be inserted in place of deleted characters from the starting index.

 Lets look at an example of STUFF function in SQL Server.

Using STUFF function with starting position 1 removing nine characters and replacing a substring

Following example uses the STUFF function to delete the first nine characters from given string  ‘Microsoft @2018’ and then insert the string ‘Microsoft SQL Server’ at the beginning of the string .

DECLARE @string AS VARCHAR(25); 
SET @string = 'Microsoft @2018' 
SELECT STUFF(@string, 1, 9, 'Microsoft SQL Server') As output;

Using STUFF function with starting position 3 and removes zero characters

Following example uses STUFF function to insert the string ‘crosof’ at third position of given string as length is passed to zero, so it does not remove any character from given string.

Declare @string as varchar(30);
SET @string = 'MIT SQL SERVER @2018' 
SELECT STUFF(@string, 3 , 0, 'CROSOF') As output;

 

STUFF Function with negative Length value

If negative length values is passed to STUFF function, It returns null values as shown in below screenshot.

DECLARE @string AS VARCHAR(30); 
SET @string = 'Mit SQL Server @2018' 
SELECT STUFF(@string, 3, -1, 'crosof') AS [OUTPUT];

STUFF Function when starting Position is greater than string Length

SUTFF Function returns null when you provide a starting position that is greater than input string length.

The Length of input string @string =’SQL’ is three while the starting position value as second argument is passed 4. You can see the output returned as NULL by following statement.

DECLARE @string AS VARCHAR(30);
SET @string = 'SQL' 
SELECT STUFF(@string, 4, 0, 'Server') AS [OUTPUT];

Also Read..

SQL String Functions

UPPER()

LOWER()