SQL Server STUFF Function: Insert Substring into String

In SQL Server, the STUFF() function inserts a string into another string. It deletes a specified number of characters from the first string starting at the specified position and inserts the given second string from that position.

STUFF(string_expression, start, length, replacementString)

Parameters

1. string_expression: A string of character data. It can be a constant, variable, or a table column of character or binary data.
2. Start: An integer that specifies the position from where the deletion and insertion start. If the start is negative, zero, or longer than the first expression, then the STUFF() function returns NULL. Start can also be of type bigint.
3. Length: The number of characters to delete from the first string. It is an integer.
4. replacementString: A substring that should be replaced in the string_expression.

Return Value

Returns character data if string_expression is of character data type.

• Returns NULL if the length is negative.
• If the length is longer than the first string or string_expression, then the whole string_expression is deleted.
• If the length is zero, then the function will insert the replacementString at the beginning of the string_expression. No characters are deleted from the string_expression.

Example 1:

In the following simple example, a given string 'abcdefgh' is replaced from the third character to a length of six characters, by 'xxxx'.

SELECT STUFF('abcdefgh',3,6,'xxxx') AS Result;

Example 2:

In the following example, 'nice' is deleted and replaced with the word 'good'.

SELECT STUFF('Have a nice day!',8,4,'good') AS Result

Example 3:

In the following example dempnstrate different start and length parameters.

SELECT STUFF('Have a nice day!',0, 4, 'good') AS Result1, 
STUFF('Have a nice day!', 1, 4, 'good') AS Result2,  
STUFF('Have a nice day!',6, 2, 'good') AS Result3;

Example 4

In the following example, the STUFF() function is used in masking sensitive data like the passport number. The first 5 characters of the passport number are replaced with 'xxxxx' as shown in the query below.

SELECT EmployeeID, STUFF(PassportNumber, 1, 5, 'xxxxx') FROM EmployeeDetails

Example 5

In this example, the length is a negative integer. The return value is NULL.

SELECT STUFF('Have a nice day!',1,-4,'good') AS Result;

Example 6

In the below example, the replacementString is an empty string. So the string_expression starting from the eighth position to a length of 4 is replaced by a space as shown in the result.

SELECT STUFF('Have a nice day!',8,4,'') AS Result;

Example 7

In this example, the STUFF() function is used to format a date from MMDDYYYY format to MM/DD/YYYY format. Two STUFF() functions are used to insert /. One between DD and MM and another between MM and YYYY, as shown below.

SELECT STUFF(STUFF('01022022', 3,0,'/'),6,0,'/') AS Result;