SQL STUFF function overview
SQL STUFF function overview
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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolSQL STUFF function overview
July 31, 2019 by Rajendra Gupta 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. 123 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. 123 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. 123 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. 123 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 br
In this example, we will start at a position 9 and remove 10 characters and STUFF substring at 9th position. 123 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. 123 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. 123 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 12 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. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022