STUFF Function in SQL Server

In this SQL Server tutorial, I will explain how to use the STUFF function in SQL Server, which helps in string manipulations.

I will begin to describe the ‘What is STUFF() function?’ with syntax, and with examples, you will understand how it manipulates the string.

Then, you will use the STUFF() function to format the value, such as date and time.

Table of Contents

The STUFF function in SQL Server deletes the specified length of characters from the given string and inserts the new string at the specified starting point in the string.

This function is very helpful in data clearing or data science, as well as in formatting data when you need to display the data in a proper format.

The syntax is given below.

STUFF(string_value, start_pos, length, new_string);

Where,

STUFF(): This function removes the characters’ specific length from the string and inserts the new string from the specified starting index.
string_value: It is the source string in which a specific length of characters is removed, and a new one is inserted from the specified position. It can be any column, constant, variable, character or binary data.
start_pos: It is the position or the index where the deletion or insert of the string begins. If you pass the start value as the negative or zero, it returns the NULL.
length: The number of characters you want to delete from the string from the specified starting position.
new_string: This is the new string inserted in place of the deleted string from the specified starting position in the source string.

Let’s take an example and understand the working of the STUFF function in SQL Server.

Suppose you have the string ‘I live in Chicago’ and want to remove the string ‘Chicago’ and insert the new string ‘New York’. So, for that, use the query below.

SELECT STUFF('I live in Chicago',11,7, 'New York') AS StuffedString;

from the output of the above query that you can see in the picture, the word ‘Chicago’ is deleted from the string and inserted in the new string ‘New Your’ at the specified starting position.

Let’s understand the query part ‘STUFF(‘I live in Chicago’,11,7, ‘New York’)’, here first the source string ‘I live in Chicago’, second is the starting position of the string part that you want to delete which is 11, that means delete all the characters from this index in the string.

After that, the third is the 7, which means deleting a total of seven characters from the index 11; then last is the ‘New York’ string, which means inserting this string from the position 11.

Overall, you have deleted 7 characters from the index 11 in the string and inserted a new string in place of the deleted string. This is how to use the STUFF function in SQL Server.

Formatting Value using STUFF Function in SQL Server

Using the STUFF function in SQL Server, you can format the value such as the date and time value; if you have raw datetime value, where it doesn’t contain any separator to indicate which part is the date or time, here you can use the STUFF() function.

For example, you have time ‘1345’, which is 13 hours and 14 minutes. As you can time is not formatted, or you don’t see any colon between hours and minutes, so to include the colon, use the query below.

SELECT STUFF('1314',3,0, ':') AS FormattedTimeValue;

Formatting Time Value using STUFF Function in SQL Server

From the output, you can see the time value is formatted, or colon (:) is inserted between hours and minutes of the time value, which is more readable now. This is how to format the time value using the STUFF() function of SQL Server.

As the length value is 0, the STUFF() function doesn’t remove or delete the characters.

Next, format the date value 20240115 and use the query below.

SELECT STUFF(STUFF('20240115',5,0, '-'),8,0,'-') AS FormattedDateValue

Formatting Date Value using STUFF Function in SQL Server

Look how the date value 20240115 is formatted to 2024-01-15 using the STUFF function in SQL Server.

In the query part, STUFF(STUFF(‘20240115’,5,0, ‘-‘), 8, 0,’-‘), here nested STUFF() function is used, first STUFF(‘20240115’, 5, 0, ‘-‘), inserts the hyphen (-) at the 5th positions in the string, and the second STUFF(result, 8, 0, ‘-‘), insert the hyphen (-) at the 8th position in the string.

But, if you look at the number of characters deleted, it is 0, and no character is deleted.

In the above query, the result from the first STUFF() function is passed to the second STUFF() as the source string containing the hyphen at the 5th position, and then the second function inserts the hyphen in the string at the 8th position. Thus, the date value is formatted.

This is how to format the value using the STUFF function in SQL Server. Remember, if the starting position of the string is negative or zero or larger than the length of a specified string, then in those cases, it returns the NULL string.

Also, if the length you specified for delete characters is larger than the first string, then the STUFF() function removes the first character of the first string.

Conclusion

In this SQL Server tutorial, you have inserted the new string in the existing string by deleting certain characters from specified positions using the STUFF function in SQL Server.

With several examples, you have cleared your doubts about the STUFF() function and used the STUFF() function to format the time and date values.