Rajendra Gupta

SQL STUFF function overview

July 31, 2019 by

This article gives an overview of the SQL STUFF function with various examples.

Introduction

Developers deal with various data types and require converting the data as per the application requirements. Suppose we are working with the strings and require replacing a part of the string with the character or string. You might think of using the Replace function immediately after understanding the requirement.

Let’s make the scenario complicated. In the actual string, you have various occurrences of similar characters. You only want to replace a particular set of characters at a specific position.

Example:

String: This is an article useful for the SQL Developers.

In the string, we want to replace the following characters

Actual characters

Replace with

Word

Is

At

This

Execute the following query with the SQL REPLACE function.

In the output, we can see it replaces both instances of occurrence of characters, but it is not as per the requirement.

SQL REPLACE FUNCTION

SQL Server provides a useful function SQL STUFF to replace a specific substring with another. Many DBA or developers are not aware of this useful function. Let’s explore SQL STUFF function in the next section of this article.

Overview of SQL STUFF function

We use the STUFF function to do the following tasks.

  • Delete the number of characters from the string. We define the number of characters using the length parameter. If we define zero, it does not remove any characters from the string
  • We specify the start position in the string from where the number of the character defined using the length parameters needs to be deleted
  • We need to specify the replacement substring as well in the new substring parameter. This new string is replaced at the start position

The syntax for the SQL STUFF function is as below.

STUFF (character_expression , start , length , new_expression )

Let’s demonstrate the SQL STUFF function with some examples.

Example 1: STUFF function with starting position 1 and removes zero characters

In this example, we defined a variable with VARCHAR() data type for the string. In the string, we want to STUFF Microsoft word at position 1 without removing any characters.

We get the output Microsoft SQL Server as shown in the following screenshot.

SQL STUFF function example

Example 2: STUFF function with starting position 5 removing six characters and replacing a substring

In this example, we want to start at position 5 and remove six characters and places new substring at starting position 5.

STUFF function with starting position 5 mremoving six characters and replacing a substring

Example 3: STUFF function with starting position 5 and removes two characters and Stuff a substring

In previous examples, we replaced the complete word from the specified string. In this example, let’s remove only specific characters and STUFF the substring.

STUFF function with starting position 5 and removes two characters and Stuff a substring

Example 4: SQL STUFF function to replace a special character from the string

In this example, we want to remove a special character at stating position 1. We can use the following query to do this task for us.

STUFF function to replace a special character

Example 5: STUFF function with the starting position value larger than the string length

Suppose you have a string with an overall length of three characters. If you have specified the stating position five, what will be the output of SQL STUFF function?

Let’s look at this using an example. We always get NULL output in this case.

STUFF function with the starting position value larger than the string length

Example 6: STUFF function with the zero as the starting position

We should always start the position from number one. If we specify zero as the starting position, it also returns NULL as an output.

STUFF function with the zero as the starting position

Example 7: Using STUFF function to remove and stuff characters more than the existing length

In this example, we will start at a position 9 and remove 10 characters and STUFF substring at 9th position.

We do not get any error message or the NULL value as output. We only have a character at the 9th position. Therefore, it removes the specific character and replaces it with a substring.

STUFF function with the number of character to remove more than the existing length

Example 8: STUFF function with a negative start position value

Let’s specify a negative value in the start position parameter value and observe the output.

We always get NULL values for the negative value in the start position for the SQL STUFF function as well.

STUFF function with a negative start position value

Similarly, we cannot use a negative value in the length parameter as well. It also returns NULL value in the output.

SQL STUFF function with a negative lenth value

Example 9: STUFF function to format date from DDMMYYYY format to DD/MM/YYYY format

Suppose we have the data field in the table and we store data in the format DDMMYYYY. In the application reports, we want to display it in the format of DD/MM/YYYY.

Let’s use the SQL STUFF function to convert the date format. In this query, we stuff forward slash at specific position 3 and 6. We need to use the STUFF function twice in this case.

In the following screenshot, we can see that the date format is DD/MM/YYYY.

STUFF function to format date from DDMMYYYY format to DD/MM/YYYY format

Example 10: STUFF function to mask sensitive information

Suppose we have a customer table and contains the 10-digit account number for all customers. We do not want to display it in the application and mask it before displaying the data. We want to display only the last three digits of the customer’s bank account numbers.

We can use the STUFF function to mask sensitive information. In this query, we use the following functions.

  • LEN() function to check the length of the bank account number
  • Starting position 1
  • Replication character X to the length of account number minus the three

STUFF function to mask sensitive information

Conclusion

In this article, we explored the useful SQL STUFF function to replace a substring with another string at a specified position with several examples. You should explore this function in the lab environment to get more familiar with it.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

645 Views