IP Address: 34.203.225.78



SQL Server 2017为我们带来了一些新的T-SQL函数。他们是非常简单的,可以帮助我们简化我们的T-SQL代码。现在就谈一谈这些新的T-SQL函数。

String_AGG

这个新的函数解决了一个古老的有趣的问题:如何将多条记录连接成一个字符串值。

有几种情况可能会有这类需求,例如当一个人有几个电子邮件地址,几个电话号码时,我们想把这个人的电子邮件地址和电话号码都打印出来。

这在之前 的T-SQL中几乎是不可能的,我们需要使用一些XML的技术来实现此功能。

我们举一个例子,通过下面的脚本来创建表并添加一些记录:

drop tableif exists names
create table names 
( [name] varchar(50) )
go
 
insert into names values (‘joao’),(‘jose’),(‘maria’),(‘joaquim’)
go

下面的查询语句使用了一些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 
       from person.person, person.EmailAddress 
       where person.BusinessEntityID=EmailAddress.BusinessEntityID 
       group by lastname

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

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 
       from person.person, person.EmailAddress 
       where person.BusinessEntityID=EmailAddress.BusinessEntityID 
       group by lastname

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;
 

select Concat_WS(char(13),addressline1,addressline2,city,PostalCode) 
       as [Address],AddressId 
       from person.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(‘(137.4 72.3)’ , ‘( )’, ‘[,]’) AS Coordinates

由Translate取代了原来的replace函数,Translate函数允许我们对整个字符串进行整体替换。

发表评论

电子邮件地址不会被公开。 必填项已用*标注