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

查询结果如下图所示：

SQL Server 2017中新的T-SQL函数

全新的STRING_AGG函数可以得到同样的结果：

select string_agg([name],‘,’) from names

AdventureWorks数据库对此函数提供了另外一个有趣的例子。表‘Person.Person’和‘Person.EmailAddress’相关联，并且每个人可以具有多个邮件地址。现在有一个需求是在一条记录中列出某个人的所有邮件地址。

以下的查询可以实现此功能，这里有一个异常：

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

查询结果会出现如下报错：

SQL Server 2017中新的T-SQL函数

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函数允许我们对整个字符串进行整体替换。