○

SQL Server之深入理解STUFF

STUFF字符串函数是将字符串插入到另一个字符串中。它会删除开始位置第一个字符串中的指定长度的字符，然后将第二个字符串插入到开始位置的第一个字符串中，语法如下：

> **STUFF（character_expression，<开始>，<长度>，character_expression）**

<character_expression>参数是给定的字符串数据，可以是字符或二进制数据的常量，变量或列。
<start>参数是一个整数值，指定开始删除和插入的位置，可以是BIGINT类型。如果<开始>或<长度>参数为负数，则返回NULL字符串。如果<start>参数比第一个<character_expression>长，则返回一个NULL字符串。
<length>参数可以是BIGINT类型，它是一个整数，指定要删除的字符数。如果<length>比第一个<character_expression>长，则删除发生到最后一个<character_expression>中的最后一个字符。
```sql
DECLARE @FullName   VARCHAR(100)
DECLARE @Alias      VARCHAR(20)

SET @FullName = 'Jeffcky Wang'
SET @Alias = ' "Superman" '

SELECT STUFF(@FullName, CHARINDEX(' ', @FullName), 1, @Alias) AS [FullName]
```
返回结果：

|   | FullName  |
| ------------ | ------------ |
| 1  |  Jeffcky "Superman" Wang |

如上STUFF函数中的第一个参数我们给定的是 @FullName，第二个是开始的位置，我们通过CHARINDEX函数找出 @FullName以空格隔开的的位置返回，最后由 @Alias来代替，结果如图所示。

```sql
DECLARE @Time VARCHAR(10)
SET @Time = '1030'

SELECT STUFF(@Time, 3, 0, ':') AS [HH:MM]
```
返回结果：

|   | HH:MM  |
| ------------ | ------------ |
| 1  |  10:30 |

我们给定的字符串为@Time即1030，我们从第3个位置开始，删除长度为0，此时则在3前面插入冒号，结果如上图输出10:30。

```sql
DECLARE @CreditCardNumber  VARCHAR(20)
SET @CreditCardNumber = '370200199408103544'

SELECT STUFF(@CreditCardNumber, LEN(@CreditCardNumber) -3, 4, 'XXXX') AS [Output]

select REPLACE('513723199007071670', SUBSTRING('513723199007071670',15,4), 'XXXX')
```
返回结果：

|   | OUTPUT |
| ------------ | ------------ |
| 1  |  37020019940810XXXX |

如上我们将身份证通过STUFF将最后四位用XXXX代替。

以上是STUFF最基础的用法。STUFF最常见的用途莫过于结合FOR XML PATH对返回JSON字符串的拼接。首先利用FOR XML PATH则返回XML格式的字符串，我们将FOR XML PATH添加到查询的末尾，此时允许我们将查询的结果作为XML元素输出，元素名称包含在PATH参数中。
```sql
SELECT TOP 5 ',' + Name 
              FROM  Production.Product
              FOR XML PATH ('')

返回：,Adjustable Race,All-Purpose Bike Stand,AWC Logo Cap,BB Ball Bearing,Bearing Ball
```
此时我们利用STUFF将上述利用FOR XML PATH生成的字符串中的前置逗号去掉，如下：

```sql
SELECT Name = STUFF((
            SELECT TOP 5 ',' + NAME
            FROM Production.Product
            FOR XML PATH('')
            ), 1, 1, '')

返回：Adjustable Race,All-Purpose Bike Stand,AWC Logo Cap,BB Ball Bearing,Bearing Ball
```

比如我们要查询各种产品中的产品列表名称，最后我们改造成如下：
```sql
SELECT TOP 5 p2.ProductID, Name = STUFF((
            SELECT ',' + NAME
            FROM Production.Product AS p1
            WHERE p1.ProductID = p2.ProductID
            FOR XML PATH('')
            ), 1, 1, '') FROM Production.Product AS p2
GROUP BY p2.ProductID
```
![](../upload/img/2021011140097329.png)

接下来我们利用STUFF结合FOR XML PATH来拼接JSON字符串，如下：
```sql
DECLARE @content VARCHAR(MAX)

SET @content = (SELECT '['+ STUFF((SELECT TOP 5 ',{"ProductName": "' + ProductName + '","Price": "' + CONVERT(VARCHAR, Price) + '","Quantity": "' + CONVERT(VARCHAR, quantity) + '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList 
 FOR XML PATH('')), 1, 1,''
 ) 
 + ']'[ProductDetail])

PRINT @content
```
![](../upload/img/2021011140138769.png)

结果如上正确输出JSON字符串，接下来我们将如上拼接换行再试试。

```sql
SET @content = ( SELECT '['
                        + STUFF(( SELECT TOP 5
                                            ',{"ProductName": "' + ProductName
                                            + '","Price": "'
                                            + CONVERT(VARCHAR, Price)
                                            + '","Quantity": "'
                                            + CONVERT(VARCHAR, quantity)
                                            + '","Inserton": "'
                                            + CONVERT(VARCHAR, Inserton, 105)
                                            + '"}'
                                  FROM      ProductList
                                FOR
                                  XML PATH('')
                                ), 1, 1, '') + ']' [ProductDetail]
               )

PRINT @content
```
如上是利用SQL Prompt直接格式化换行，结果依然正确输出JSON字符串，我们再来手动换行试试。
```sql
DECLARE @content VARCHAR(MAX)

SET @content = (SELECT 
'['+ STUFF((SELECT TOP 5 ',
{"ProductName": "' + ProductName 
+ '","Price": "' + CONVERT(VARCHAR, Price) 
+ '","Quantity": "' + CONVERT(VARCHAR, quantity) 
+ '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) 
+ '"}' FROM ProductList 
 FOR XML PATH('')), 1, 1,''
 ) 
 + ']'[ProductDetail])

PRINT @content
```

![](../upload/img/2021011140210845.png)
结果输出如上我们不期望的字符串，主要是由FOR XML PATH造成的，比如我们利用FOR XML PATH进行如下查询：
```sql
SELECT  '    '
FOR     XML PATH('') 
```
![](../upload/img/2021011140286065.png)

当我们利用FOR XML  PATH查询数据时，如果字符串中包含空格时会造成出现以如上错误的字符串来填充，所以此时我们为了消除这种错误格式，我们将上述继续添加参数。
```sql
SELECT  '    '
FOR     XML PATH(''),TYPE 
```
![](../upload/img/2021011140328417.png)
此时我们将上述输出JSON字符串不错误的格式修改成如下即可：
```sql
DECLARE @content VARCHAR(MAX)

SET @content = (SELECT 
'['+ STUFF((SELECT TOP 5 ',
{"ProductName": "' + ProductName 
+ '","Price": "' + CONVERT(VARCHAR, Price) 
+ '","Quantity": "' + CONVERT(VARCHAR, quantity) 
+ '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList 
 FOR XML PATH('') ,TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,''
 ) 
 + ']'[ProductDetail])

PRINT @content
```
![](../upload/img/2021011140369145.png)
或者我们对上述输出的错误字符串进行替换，如下：
```sql
select t.PK, 
    ltrim(rtrim(replace(
    (select ' ' + isnull(ti.Column1, '') + ' ' + isnull(ti.Column2, '')
     from yourTable ti 
     where ti.PK = t.PK
     for xml path (''))
     , ' ', ''))) fruits
from yourTable t
group by t.PK;
```
这里我们解决了利用STUFF有可能输出JSON字符串带有错误的字符串的问题，在利用STUFF输出JSON字符串时只要有一列数据包含NULL，那么返回的数据则为空，那么我们在对列数据通过ISNULL来进行判断，比如如下将输出NULL。
```sql
DECLARE @content VARCHAR(MAX)

SET @content = (SELECT 
'['+ STUFF((SELECT TOP 5 ',
{"ProductName": "' + NULL 
+ '","Price": "' + CONVERT(VARCHAR, Price) 
+ '","Quantity": "' + CONVERT(VARCHAR, quantity) 
+ '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList 
 FOR XML PATH('') ,TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,''
 ) 
 + ']'[ProductDetail])

PRINT @content
```
所以此时我们必须通过ISNULL来判断列数据是否为NULL，修改成如下形式：

```sql
DECLARE @content VARCHAR(MAX)

SET @content = (SELECT 
'['+ STUFF((SELECT TOP 5 ',
{"ProductName": "' + ISNULL(ProductName,'') 
+ '","Price": "' + CONVERT(VARCHAR, Price) 
+ '","Quantity": "' + CONVERT(VARCHAR, quantity) 
+ '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList 
 FOR XML PATH('') ,TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,''
 ) 
 + ']'[ProductDetail])

PRINT @content
```

技术笔记