sql stuff 函数
This article gives an overview of the SQL STUFF function with various examples.
本文通过各种示例概述了SQL STUFF函数。
介绍 (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.
字符串：这是一篇对SQL Developers有用的文章。
In the string, we want to replace the following characters
在字符串中，我们要替换以下字符
Execute the following query with the SQL REPLACE function.
使用SQL REPLACE函数执行以下查询。
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.
SQL Server提供了一个有用的函数SQL STUFF，可以用另一个替换特定的子字符串。 许多DBA或开发人员都不知道此有用的功能。 让我们在本文的下一部分中探索SQL STUFF函数。
SQL STUFF函数概述 (Overview of SQL STUFF function)
We use the STUFF function to do the following tasks.
我们使用STUFF函数执行以下任务。
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 从字符串中删除字符数。 我们使用length参数定义字符数。 如果我们定义零，它不会从字符串中删除任何字符 We specify the start position in the string from where the number of the character defined using the length parameters needs to be deleted 我们在字符串中指定开始位置，从该位置开始，需要删除使用length参数定义的字符数 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.
SQL STUFF函数的语法如下。
STUFF (character_expression , start , length , new_expression )
STUFF（character_expression，start，length，new_expression）
Let’s demonstrate the SQL STUFF function with some examples.
让我们用一些示例来演示SQL STUFF函数。
示例1：STUFF函数的起始位置为1，并删除了零个字符 (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.
在此示例中，我们为字符串定义了一个具有VARCHAR（）数据类型的变量。 在字符串中，我们要在位置1处填充Microsoft单词而不删除任何字符。
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.
我们得到输出Microsoft SQL Server，如以下屏幕快照所示。
示例2：从起始位置5开始的STUFF函数删除六个字符并替换一个子字符串 (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.
在此示例中，我们要从位置5开始并删除六个字符，并在起始位置5放置新的子字符串。
DECLARE @Character_Expression VARCHAR(50);SET @Character_Expression = 'SQL Server'; SELECT STUFF (@Character_Expression, 5, 6, 'On SQLShack') AS 'STUFF Function'
示例3：STUFF函数（从起始位置5开始）并删除两个字符，并填充一个子字符串 (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进行删除。
DECLARE @Character_Expression VARCHAR(50);SET @Character_Expression = 'SQL Server'; SELECT STUFF (@Character_Expression, 5, 2, 'AB') AS 'STUFF Function'
示例4：SQL STUFF函数替换字符串中的特殊字符 (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处删除特殊字符。我们可以使用以下查询为我们完成此任务。
DECLARE @Character_Expression VARCHAR(50);SET @Character_Expression = '#'; SELECT STUFF (@Character_Expression, 1, 1, '') AS 'STUFF Function'
示例5：STUFF函数的起始位置值大于字符串长度 (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?
假设您有一个总长度为三个字符的字符串。 如果已指定起始位置5，则SQL STUFF函数的输出将是什么？
Let’s look at this using an example. We always get NULL output in this case.
我们来看一个例子。 在这种情况下，我们总是得到NULL输出。
示例6：以零作为起始位置的STUFF函数 (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.
我们应该始终从第一名开始。 如果我们指定零作为起始位置，则它还会返回NULL作为输出。
示例7：使用STUFF函数删除和填充超出现有长度的字符
(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.
在此示例中，我们将从位置9开始，并从第9位删除10个字符和STUFF子字符串。
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.
我们没有收到任何错误消息或NULL值作为输出。 我们只有第9个角色。 因此，它将删除特定字符并将其替换为子字符串。
示例8：起始位置值为负的STUFF函数 (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.
让我们在起始位置参数值中指定一个负值，并观察输出。
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.
在SQL STUFF函数的开始位置，我们总是总是得到负值的NULL值。
Similarly, we cannot use a negative value in the length parameter as well. It also returns NULL value in the output.
同样，我们也不能在length参数中使用负值。 它还在输出中返回NULL值。
DECLARE @Character_Expression VARCHAR(50);SET @Character_Expression = 'SQLShack@'; SELECT STUFF (@Character_Expression, 2, -1, '.com') AS 'STUFF Function'
示例9：STUFF函数将日期格式从DDMMYYYY格式转换为DD / MM / YYYY格式 (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.
假设我们在表中有数据字段，并且我们以DDMMYYYY格式存储数据。 在应用程序报告中，我们要以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.
让我们使用SQL STUFF函数转换日期格式。 在此查询中，我们在特定位置3和6处添加正斜杠。在这种情况下，我们需要使用STUFF函数两次。
SELECT STUFF(STUFF('3007', 3, 0, '/'), 6, 0, '/') new_formatted_date;
In the following screenshot, we can see that the date format is DD/MM/YYYY.
在以下屏幕截图中，我们可以看到日期格式为DD / MM / YYYY。
示例10：STUFF函数掩盖敏感信息 (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.
假设我们有一个客户表，其中包含所有客户的10位帐号。 我们不想在应用程序中显示它并在显示数据之前对其进行遮罩。 我们只想显示客户银行帐号的后三位。
We can use the STUFF function to mask sensitive information. In this query, we use the following functions.
我们可以使用STUFF函数掩盖敏感信息。 在此查询中，我们使用以下功能。
LEN() function to check the length of the bank account number LEN（）函数检查银行帐号的长度 Starting position 1 起始位置1 Replication character X to the length of account number minus the three 复制字符X到帐号的长度减去三个
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.
在本文中，我们通过几个示例探索了有用SQL STUFF函数，该函数在指定位置用另一个字符串替换子字符串。 您应该在实验室环境中探索此功能，以使其更加熟悉。
翻译自: /sql-stuff-function-overview/
sql stuff 函数
