SQL Server之深入理解STUFF

前言

最近專案無論查詢報表還是其他資料都在和SQL Server資料庫打交道，對於STUFF也有了解，但是發現當下一次再寫SQL語句時我還得檢視相關具體用法，說到底還是沒有完全理解其原理，所以本節我們來談談STUFF，Jeff是在專案中哪裡不熟悉，哪裡不會或者哪裡耗時比較多就會去深入理解和鞏固即使是很基礎的知識，直到完全不用浪費時間去查閱相關資料，這是我的出發點。

深入理解STUFF

STUFF字串函式是將字串插入到另一個字串中。它會刪除開始位置第一個字串中的指定長度的字元，然後將第二個字串插入到開始位置的第一個字串中，語法如下。

STUFF（，，，）
引數是給定的字串資料，可以是字元或二進位制資料的常量，變數或列。引數是一個整數值，指定開始刪除和插入的位置，可以是BIGINT型別。如果或引數為負數，則返回NULL字串。如果引數比第一個長，則返回一個NULL字串。 引數可以是BIGINT型別，它是一個整數，指定要刪除的字元數。如果比第一個長，則刪除發生到最後一個中的最後一個字元。

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

如上STUFF函式中的第一個引數我們給定的是@FullName，第二個是開始的位置，我們透過CHARINDEX函式找出@FullName以空格隔開的的位置返回，最後由@Alias來代替，結果如圖所示。

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

我們給定的字串為@Time即1030，我們從第3個位置開始，刪除長度為0，此時則在3前面插入冒號，結果如上圖輸出10:30。

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

如上我們將身份證透過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 ('')

,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, '')

比如我們要查詢各種產品中的產品列表名稱，最後我們改造成如下：

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 p2GROUP BY p2.ProductID

接下來我們利用STUFF結合FOR XML PATH來拼接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

結果如上正確輸出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字串，我們再來手動換行試試。

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

結果輸出如上我們不期望的字串，主要是由FOR XML PATH造成的，比如我們利用FOR XML PATH進行如下查詢：

SELECT  '    'FOR     XML PATH('')

當我們利用FOR XML  PATH查詢資料時，如果字串中包含空格時會造成出現以如上錯誤的字串來填充，所以此時我們為了消除這種錯誤格式，我們將上述繼續新增引數。

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

此時我們將上述輸出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('') ,TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,''
 ) 
 + ']'[ProductDetail])PRINT @content

或者我們對上述輸出的錯誤字串進行替換，如下：

select t.PK, 
    ltrim(rtrim(replace(
    (select ' ' + isnull(ti.Column1, '') + ' ' + isnull(ti.Column2, '')     from yourTable ti 
     where ti.PK = t.PK     for xml path (''))
     , ' ', ''))) fruitsfrom yourTable tgroup 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

  作者：