SQL Server之深入理解STUFF

骑猪看日落 2021-06-26 16:06 413阅读 0赞

## 前言 ##

最近项目无论查询报表还是其他数据都在和`SQL Server`数据库打交道，对于`STUFF`也有了解，但是发现当下一次再写`SQL`语句时我还得查看相关具体用法，说到底还是没有完全理解其原理，所以本节我们来谈谈`STUFF`，`Jeff`是在项目中哪里不熟悉，哪里不会或者哪里耗时比较多就会去深入理解和巩固即使是很基础的知识，直到完全不用浪费时间去查阅相关资料，这是我的出发点。

## 深入理解`STUFF` ##

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

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

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

DECLARE @FullName       VARCHAR(100)
    DECLARE @Alias          VARCHAR(20)
    
    SET @FullName = 'Jeffcky Wang'
    SET @Alias = ' "Superman" '
    
    SELECT STUFF(@FullName, CHARINDEX(' ', @FullName), 1, @Alias) AS [FullName]

![在这里插入图片描述][20190529165115592.png]  
如上`STUFF`函数中的第一个参数我们给定的是`@FullName`，第二个是开始的位置，我们通过`CHARINDEX`函数找出`@FullName`以空格隔开的的位置返回，最后由`@Alias`来代替，结果如图所示。

DECLARE @Time VARCHAR(10)
    SET @Time = '1030'
    
    SELECT STUFF(@Time, 3, 0, ':') AS [HH:MM]

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

DECLARE @CreditCardNumber  VARCHAR(20)
    SET @CreditCardNumber = '370200199408103544'
    
    SELECT STUFF(@CreditCardNumber, LEN(@CreditCardNumber) -3, 4,
           'XXXX') AS [Output]

![在这里插入图片描述][20190529165153750.png]  
如上我们将身份证通过`STUFF`将最后四位用`XXXX`代替。以上是`STUFF`最基础的用法。`STUFF`最常见的用途莫过于结合`FOR XML PATH`对返回`JSON`字符串的拼接。首先利用`FOR XML PATH`则返回`XML`格式的字符串，我们将`FOR XML PATH`添加到查询的末尾，此时允许我们将查询的结果作为`XML`元素输出，元素名称包含在`PATH`参数中。。

SELECT TOP 5 ',' + Name 
                  FROM  Production.Product
                  FOR XML PATH ('')

![在这里插入图片描述][2019052916521495.png]

,Adjustable Race,All-Purpose Bike Stand,AWC Logo Cap,BB Ball Bearing,Bearing Ball

此时我们利用`STUFF`将上述利用`FOR XML PATH`生成的字符串中的前置逗号去掉，如下：

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

![在这里插入图片描述][20190529165232663.png]  
比如我们要查询各种产品中的产品列表名称，最后我们改造成如下：

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

![在这里插入图片描述][20190529165243710.png]  
接下来我们利用`STUFF`结合`FOR XML PATH`来拼接`JSON`字符串，如下：

![在这里插入图片描述][2019052916530767.png]  
结果如上正确输出`JSON`字符串，接下来我们将如上拼接换行再试试。

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

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

![在这里插入图片描述][20190529165322756.png]  
结果输出如上我们不期望的字符串，主要是由`FOR XML PATH`造成的，比如我们利用`FOR XML PATH`进行如下查询：

SELECT  ' '
    FOR XML PATH('')

![在这里插入图片描述][2019052916533452.png]  
当我们利用`FOR XML PATH`查询数据时，如果字符串中包含空格时会造成出现以如上错误的字符串来填充，所以此时我们为了消除这种错误格式，我们将上述继续添加参数。

SELECT  ' '
    FOR     XML PATH(''),TYPE

![在这里插入图片描述][20190529165356155.png]  
此时我们将上述输出`JSON`字符串不错误的格式修改成如下即可：

![在这里插入图片描述][20190529165411687.png]  
或者我们对上述输出的错误字符串进行替换，如下：

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`。

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`，修改成如下形式：

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

[20190529165115592.png]: /images/20210626/9fb3c8f5d2974e9fa6904bc057817527.png
[20190529165139664.png]: /images/20210626/f75afc9b53a04bc5bbf93af0da9eaa9d.png
[20190529165153750.png]: /images/20210626/2b68d46bd07049a5b5f486fd1ab12504.png
[2019052916521495.png]: /images/20210626/2a91137e490145cd8c493a1c8fef3543.png
[20190529165232663.png]: /images/20210626/258732b7914941e7a5a016a5e141e4be.png
[20190529165243710.png]: /images/20210626/297ab912b25c43a5a7cd59908253c427.png
[2019052916530767.png]: /images/20210626/a4fb65339de6416f99038e4c02659e46.png
[20190529165322756.png]: /images/20210626/99fb843bbb714e1f819f0f5eb2cd222a.png
[2019052916533452.png]: /images/20210626/e84e84ba60564938a876b7b8a65a4860.png
[20190529165356155.png]: /images/20210626/7022224b914d4d528fb43c8734fd663f.png
[20190529165411687.png]: /images/20210626/97914a45c69f4cf7abb63168554fbb68.png