SQL Server 2017中新的T-SQL函數

SQL Server 2017為我們帶來了一些新的T-SQL函數。他們是非常簡單的，可以幫助我們簡化我們的T-SQL代碼。現在就談一談這些新的T-SQL函數。

String_AGG

這個新的函數解決了一個古老的有趣的問題：如何將多條記錄連接成一個字元串值。

有幾種情況可能會有這類需求，例如當一個人有幾個電子郵件地址，幾個電話號碼時，我們想把這個人的電子郵件地址和電話號碼都列印出來。

這在之前 的T-SQL中幾乎是不可能的，我們需要使用一些XML的技術來實現此功能。

我們舉一個例子，通過下面的腳本來創建表並添加一些記錄：

drop tableif exists names

下面的查詢語句使用了一些XML技巧將名稱連接到一個逗號間隔的字元串中：

select stuff((select 『,』 + [name] as [text()] from names for xml path(」)),1,1,」)

查詢結果如下圖所示：

全新的STRING_AGG函數可以得到同樣的結果：

select string_agg([name],『,』) from names

AdventureWorks資料庫對此函數提供了另外一個有趣的例子。表『Person.Person』和『Person.EmailAddress』相關聯，並且每個人可以具有多個郵件地址。現在有一個需求是在一條記錄中列出某個人的所有郵件地址。

以下的查詢可以實現此功能，這裡有一個異常：

select lastname,string_agg(emailaddress,『, 『) email

查詢結果會出現如下報錯：

string_agg函數的結果的大小限制取決於數據類型，通常其結果採用varchas的數據類型，正如上面例子中使用的一樣，那麼其結果的大小限制為8000位元組。

然而，在社區技術預覽2.0版（CTP 2.0）中，string_agg函數不考慮group by欄位的計算結果。正如上面的例子中，結果並沒有超出8000位元組限制時同樣會報錯。

解決辦法是改變欄位的數據類型，可以採用Cast函數實現：

select lastname,string_agg(cast(emailaddress as varchar(max)),『, 『) email

Trim

這個函數是SQL Server的DBA們長期要求的一個功能函數。

原來刪除字元串的空格通過需要採用兩個函數，如下面的語句：

SELECT RTRIM(LTRIM( 『 test 『)) AS Result;

現在新的函數則可以實現此功能：

SELECT TRIM( 『 test 『) AS Result;

Concat_WS

Concat_WS函數的功能同SQL Server 2012出現的Concat函數類似，WS是該函數功能的增加。WS在此處是指（With Separator），這意味著新的函數可以使用分隔符將字元串值連接起來。

對於空值（Null）來說，兩個函數的結果是一樣的。

這在SQL Server中的連接行為中並不是默認行為。通常，將Null值連接起來的結果會同樣得到Null，因為人們通過認為Null並不空值，而是不確定值，所以連接起來的結果同樣是不確定值Null。

SQL Server的會話會有一個配置叫作CONCAT_NULL_YIELDS_NULL，然而這個配置已經過時了。

CONCAT函數CONCAT_WS都會忽略默認行為和CONCAT_NULL_YIELDS_NULL的配置項，並在連接時忽略Null值。

這些改變在連接欄位值時簡化查詢會變得很有用，因為有些欄位可以填寫內容也可不填寫內容。

下面的兩條查詢，第一個使用逗號分隔，第二個使用回車符（char(13))進行分隔：

SELECT CONCAT_WS(『,』,『1 Microsoft Way』, NULL, NULL, 『Redmond』, 『WA』, 98052) AS Address;

這個函數對於生成報表非常有用，因為對於不同欄位的數據需要有不同的分隔符進行分隔。例如分號「；」，甚至空值Null，但當欄位值為空時，Concat_WS函數並不會添加Null分隔。

Translate

Translate函數做了幾個替換函數的工作，簡化了一些查詢語句。

這個函數之所以被使用翻譯（Translate），因為這個函數的主要目的是通過一系列的替換，把一種信息轉換為另一種信息。例如GeoJson和WKT是坐標兩個不同的格式。在GeoJson的一個坐標是使用格式『[137.4, 72.3]』表示，而對應WKT使用格式表示的 『(137.4 72.3)』。

我們可能有時需要GeoJson和WKT數據格式的雙向轉換，Translate則很容易實現此功能。

使用替換函數的語句如下所示：

select replace(replace(replace(『[137.4, 72.3]』,『[『,『(『),『,』,『 『),『]』,『)』) as Point

而使用Translate函數的查詢語句則相對簡單：

SELECT TRANSLATE(『[137.4, 72.3]』 , 『[,]』, 『( )』) AS Point,

由Translate取代了原來的replace函數，Translate函數允許我們對整個字元串進行整體替換。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！