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)
- string_expression: A string of character data. It can be a constant, variable, or a table column of character or binary data.
- 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.
- Length: The number of characters to delete from the first string. It is an integer.
- replacementString: A substring that should be replaced in the string_expression.
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.
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;
In the following example, 'nice' is deleted and replaced with the word 'good'.
SELECT STUFF('Have a nice day!',8,4,'good') AS Result
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;
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
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;
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;
In this example, the
STUFF() function is used to format a date from
MMDDYYYY format to
STUFF() functions are used to insert
/. One between
MM and another between
YYYY, as shown below.
SELECT STUFF(STUFF('01022022', 3,0,'/'),6,0,'/') AS Result;