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.
|
1 |
SELECT REPLACE('This is an article useful for the SQL Developers.','is','at') String; |
In the output, we can see it replaces both instances of occurrence of characters, but it is not as per the requirement.
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.
|
1 2 3 |
DECLARE @Character_Expression VARCHAR(50); SET @Character_Expression = 'SQL Server'; SELECT STUFF(@Character_Expression, 1, 0, ' Microsoft ') AS 'STUFF function'; |
We get the output Microsoft SQL Server as shown in the following screenshot.
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.
|
1 2 3 |
DECLARE @Character_Expression VARCHAR(50); SET @Character_Expression = 'SQL Server'; SELECT STUFF (@Character_Expression, 5, 6, 'On SQLShack') AS 'STUFF Function' |
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.
|
1 2 3 |
DECLARE @Character_Expression VARCHAR(50); SET @Character_Expression = 'SQL Server'; SELECT STUFF (@Character_Expression, 5, 2, 'AB') AS 'STUFF Function' |
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.
|
1 2 3 |
DECLARE @Character_Expression VARCHAR(50); SET @Character_Expression = '#SQLShack.com'; SELECT STUFF (@Character_Expression, 1, 1, '') AS 'STUFF Function' |
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.
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.
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.
|
1 2 3 |
DECLARE @Character_Expression VARCHAR(50); SET @Character_Expression = 'SQLShack@'; SELECT STUFF (@Character_Expression, 9, 10, '.com') AS 'STUFF Function' |
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.
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.
|
1 2 3 |
DECLARE @Character_Expression VARCHAR(50); SET @Character_Expression = 'SQLShack@'; SELECT STUFF (@Character_Expression, -2, 1, '.com') AS 'STUFF Function' |
We always get NULL values for the negative value in the start position for the SQL STUFF function as well.
Similarly, we cannot use a negative value in the length parameter as well. It also returns NULL value in the output.
|
1 2 3 |
DECLARE @Character_Expression VARCHAR(50); SET @Character_Expression = 'SQLShack@'; SELECT STUFF (@Character_Expression, 2, -1, '.com') AS 'STUFF Function' |
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.
|
1 |
SELECT STUFF(STUFF('30072019', 3, 0, '/'), 6, 0, '/') new_formatted_date; |
In the following screenshot, we can see that the date format is DD/MM/YYYY.
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
|
1 2 |
DECLARE @AccountNumber VARCHAR(10)= '6782403967'; SELECT STUFF(@AccountNumber, 1, LEN(@AccountNumber) - 3, REPLICATE('X', LEN(@AccountNumber) - 3)) MaskAccountNumber; |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023