SQL Server STUFF() Function

This article will describe a complete overview of the STUFF function in SQL Server. Most of the DBA who works with Microsoft SQL Server have a situation to change the part of the data that has been entered. This situation might happen due to human error or a change in data. The STUFF function is used to accomplish this task. This function always works with the strings to replace a portion of the string with a given character or string.

SQL Server also has a REPLACE function to accomplish the same task. The difference which makes the STUFF function to be popular on REPLACE function is that "the REPLACE function replaces all occurrences of the character but the STUFF function replaces only specific substring."

What is STUFF() Function?

The STUFF function in SQL Server is used to delete a portion of characters of a specific length from the source string and then insert the same sequence of characters from the provided starting position.

This function do the following task:

• It first removes the specified length of characters from the string. The length option is used to specify the number of characters. If we set the value to zero, no characters are removed from the string.
• The start position in the string from which the number of characters defined by the length option must be erased is specified.
• In the new substring parameter, we must also supply the replacement substring. This new string is added to the beginning of the string.

Syntax

The following syntax illustrates the STUFF function in SQL Server:

This function has four parameters, which are described below:

Source_string: It indicates the original string that we are going to modify.

Start: It indicates an integer value that specifies where deletion and insertion should begin. The function will return NULL if its value is negative, zero, or longer than the length of the string.

Length: It indicates how many characters will be removed. It returns NULL if the length is negative. It will remove the entire string if the length is more than the length of the source string. If it is 0, the replace substring will be appended to the beginning of the source string.

Replace_string: It indicates the substring that will be added in place of the deleted substring from the given start position.

STUFF() Function Example

Let us understand how the STUFF() function works in SQL Server with various examples.

Example 1: This example uses the STUFF() function to add a substring into the source string at a given position. The below statement removes the first four characters of the string 'Java Tutorial' and replaces them with the string 'SQL Server':

Executing the statement displays the below output after replacing the JAVA with SQL Server:

Example 2: This example uses the STUFF function to add a substring without removing any characters. The below statement will insert the substring Microsoft at position 1 without removing any characters:

Executing the statement displays the below output that successfully added the Microsoft at the beginning of the input string:

Example 3: This example uses the STUFF function to convert time from HHMM to HH:MM. The below statement will insert a colon (:) in the middle of an HHMM time value format and display the new time value in the format HH:MM:

Executing the statement displays the below output that successfully added a colon (:) in the middle of an HHMM:

Example 4: This example uses the STUFF function to convert the date from MMDDYYY to MM/DD/YYYY format. The below statement will call the function twice and insert a forward slash (\) in the date value format to display the new date value in the format MM/DD/YYY:

Executing the statement displays the below output that successfully converts the date MMDDYYY to MM/DD/YYYY format:

Example 5: This example uses the STUFF function in which the starting position value is larger than the original string length. Suppose we have a string 'SQL Server' with a length of ten characters. Let us see what will the result of the STUFF function if we provide a starting position twelve?

Executing the statement will return the below output as the starting position is longer than the input string:

If we provide the negative value in the start position, the STUFF function always returns a NULL value. The below image explains it more clearly:

Example 6: This example uses the STUFF function to mask sensitive information. Suppose we want to mask the credit card number and reveals only the last four characters. It can be done with the STUFF function easily. Here is the query:

This query uses the following function:

• It first declares the credit card number.
• Next, check the length of the credit card number using the LEN() function.
• Next, start masking from position 1.
• Finally, replication starts from the starting number with the character X to the length of the credit card number minus the four.

When we execute the statement, we will see the credit card number reveals only the last four characters:

Conclusion

This article will give a detailed explanation of the STUFF function in SQL Server. Here we have seen how this function replaces a substring with another string at a specified position using several examples.