最近項目無論報表查詢還是其他數據處理都在和STUFF函數打交道，之前對于STUFF也有了解，但是發現當下一次再寫SUFF SQL語句時我還得查看相關具體用法，說到底還是沒有完全理解其原理，所以本篇博文将深入說明STUFF的相關知識。

STUFF字符串函數是将字符串插入到另一個字符串中，STUFF最常見的用途結合FOR XML PATH對返回JSON字符串的拼接。

STUFF字符串函數是将字符串插入到另一個字符串中。它會删除開始位置第一個字符串中的指定長度的字符，然後将第二個字符串插入到開始位置的第一個字符串中，語法如下：

STUFF（character_expression，<開始>，<長度>，character_expression）

參數是給定的字符串數據，可以是字符或二進制數據的常量，變量或列。

參數是一個整數值，指定開始删除和插入的位置，可以是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]

返回結果：

	FullName
1	Jeffcky “Superman” Wang

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

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。

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最基礎的用法。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, '') 返回：Adjustable Race,All-Purpose Bike Stand,AWC Logo Cap,BB Ball Bearing,Bearing Ball

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

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

接下來我們利用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字符串，接下來我們将如上拼接換行再試試。

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

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部