sqlstuff函数_SQLSTUFF函数概述
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. Suppo 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 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 t of characters at a specific position.
让我们使情况变得复杂。 在实际的字符串中，您会多次出现相似的字符。 您只想在特定位置替换⼀组特定的字符。
Example:
例：
String: This is an article uful for the SQL Developers.
字符串：这是⼀篇对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.
使⽤SQL REPLACE函数执⾏以下查询。
SELECT REPLACE('This is an article uful for the SQL Developers.','is','at') String;
In the output, we can e it replaces both instances of occurrence of characters, but it is not as per the requirement.
在输出中，我们可以看到它替换了两个出现字符的实例，但这不是按照要求的。
SQL Server provides a uful function SQL STUFF to replace a specific substring with another. Many DBA or developers are not aware of this uful function. Let’s explore SQL STUFF function in the next ction of this article.
SQL Server提供了⼀个有⽤的函数SQL STUFF，可以⽤另⼀个替换特定的⼦字符串。 许多DBA或开发⼈员都不知道此有⽤的功能。 让我们在本⽂的下⼀部分中探索SQL STUFF函数。
SQL STUFF函数概述 (Overview of SQL STUFF function)
We u 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 u 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)
Suppo 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 ca.
我们来看⼀个例⼦。 在这种情况下，我们总是得到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 obrve 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 u 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)
Suppo 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 u 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 u the STUFF function twice in this ca.
让我们使⽤SQL STUFF函数转换⽇期格式。 在此查询中，我们在特定位置3和6处添加正斜杠。在这种情况下，我们需要使⽤STUFF函数两次。
SELECT STUFF(STUFF('30072019', 3, 0, '/'), 6, 0, '/') new_formatted_date;自动挡的车怎么开
In the following screenshot, we can e that the date format is DD/MM/YYYY.
在以下屏幕截图中，我们可以看到⽇期格式为DD / MM / YYYY。
⽰例10：STUFF函数掩盖敏感信息 (Example 10: STUFF function to mask nsitive information)
Suppo 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 u the STUFF function to mask nsitive information. In this query, we u 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;
