SQL Server 2012/2016/2017 新增函数

10人阅读 评论(0) 收藏 举报
分类:


/**************************************************************  
SQL Server 2012 新增的函数  
***************************************************************/  
  
--  CONCAT ( string_value1, string_value2 [, string_valueN ] ) #字符串相连  
SELECT CONCAT('A','BB','CCC','DDDD')  
--结果:ABBCCCDDDD  
  
--  PARSE ( string_value AS data_type [ USING culture ] ) #转换为所请求的数据类型的表达式的结果  
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;  
SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result;  
  
SET LANGUAGE 'English';  
SELECT PARSE('12/16/2010' AS datetime2) AS Result;  
  
/*结果:  
2010-12-13 00:00:00.0000000  
345.98  
2010-12-16 00:00:00.0000000  
*/  
  
--  TRY_CAST 、TRY_CONVERT、TRY_PARSE  (TRY_PARSE 仅用于从字符串转换为日期/时间和数字类型)  
SELECT TRY_CAST('test' AS float),TRY_CAST(5 AS VARCHAR)  
SELECT TRY_CONVERT(float,'test'),TRY_CONVERT(VARCHAR,5)  
SELECT TRY_PARSE('test' AS float),TRY_PARSE('01/01/2011' AS datetime2)  
/*结果:  
NULL    5  
NULL    5  
NULL    2011-01-01 00:00:00.0000000  
*/  
  
  
--  CHOOSE ( index, val_1, val_2 [, val_n ] ) #返回指定索引处的项 (即返回第几个值)  
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;  
--结果:Developer  
  
--  IIF ( boolean_expression, true_value, false_value )   
SELECT IIF ( 10 > 5, 'TRUE', 'FALSE' ) AS Result;  
SELECT (CASE WHEN 10 > 5 THEN 'TRUE' ELSE 'FALSE' END) AS Result;  
--结果:TRUE  
  
  
--  排名函数!  
SELECT *  
,ROW_NUMBER ( ) OVER (PARTITION BY MyName ORDER BY Num) AS 'ROW_NUMBER' --按顺序排名  
,DENSE_RANK ( ) OVER (PARTITION BY MyName ORDER BY Num) AS 'DENSE_RANK' --同排名的后面排名连续  
,RANK  ( ) OVER (PARTITION BY MyName ORDER BY Num) AS 'RANK'            --同排名的后面排名不连续  
,NTILE (2) OVER (PARTITION BY MyName ORDER BY Num) AS 'NTILE'           --按总数分两组,顺序排名  
FROM (VALUES('AA',55),('AA',30.5),('BB',55),('BB',99),('BB',0),('BB',55))AS T(MyName,Num)  
ORDER BY MyName,Num  
/*  
MyName  Num     ROW_NUMBER  DENSE_RANK  RANK    NTILE  
------  -----   ----------  ----------  ------  -----  
AA      30.5    1           1           1       1  
AA      55.0    2           2           2       2  
BB      0.0     1           1           1       1  
BB      55.0    2           2           2       1  
BB      55.0    3           2           2       2  
BB      99.0    4           3           4       2  
*/  
  
  
--  分析函数!  
SELECT *   
,CUME_DIST( )OVER (PARTITION BY MyName ORDER BY Num) AS 'CUME_DIST'     --相对(最大值)位置  
,PERCENT_RANK( )OVER (PARTITION BY MyName ORDER BY Num) AS 'PERCENT_RANK' --相对排名,排名分数参考 CUME_DIST  
,FIRST_VALUE (MyName)OVER ( ORDER BY Num ASC) AS 'FIRST_VALUE'          --Num 最低的是哪个MyName  
,LAST_VALUE  (MyName)OVER ( ORDER BY Num ASC) AS 'LAST_VALUE'           --Num 排序选底部的那个MyName  
,LAG (Num,1,0)OVER (ORDER BY Num ASC) AS 'LAG'      --上/下一行(或多行)的值移到下/上一行(或多行),方便对比  
,LEAD (Num,1,0)OVER (ORDER BY Num ASC) AS 'LEAD'    --与LAG一样,排序相反  
,PERCENTILE_CONT(0.5)WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY MyName) AS 'PERCENTILE_CONT' --连续分布计算百分位数  
,PERCENTILE_DISC(0.5)WITHIN GROUP (ORDER BY Num) OVER (PARTITION BY MyName) AS 'PERCENTILE_DISC' --离散分布计算百分位数  
FROM (VALUES('AA',55),('AA',30.5),('BB',55),('BB',99),('BB',0),('BB',55))AS T(MyName,Num)  
ORDER BY Num ASC  
  
/*  
MyName  Num     CUME_DIST   PERCENT_RANK    FIRST_VALUE LAST_VALUE  LAG     LEAD    PERCENTILE_CONT PERCENTILE_DISC  
------  -----   ---------   ------------    ----------- ----------  -----   -----   --------------- ---------------  
BB      0.0     0.25        0               BB          BB          0.0     30.5    55              55.0  
AA      30.5    0.5         0               BB          AA          0.0     55.0    42.75           30.5  
AA      55.0    1           1               BB          BB          30.5    55.0    42.75           30.5  
BB      55.0    0.75        0.33333         BB          BB          55.0    55.0    55              55.0  
BB      55.0    0.75        0.33333         BB          BB          55.0    99.0    55              55.0  
BB      99.0    1           1               BB          BB          55.0    0.0     55              55.0  
*/  
  
/**************************************************************  
SQL Server 2014 新增的函数  
***************************************************************/  
  
--貌似没有什么  
  
/**************************************************************  
SQL Server 2016 新增的函数  
***************************************************************/  
  
--  STRING_SPLIT ( string , separator ) #字符分割  
SELECT value FROM STRING_SPLIT('A,B,C',',')  
/*结果:  
value  
-----  
A  
B  
C  
*/  
  
--  STRING_ESCAPE( text , type )  #特殊字符转成带有转义字符的文本(type只支持json)  
SELECT STRING_ESCAPE('\   /  \\    "     ', 'json') AS escapedText;  
--结果:\\   \/  \\\\    \"       
  
  
--  DATEDIFF_BIG ( datepart , startdate , enddate ) #日期之间的计数  
SELECT DATEDIFF(day, '2005-12-12', '2017-10-10'); --以前版本  
SELECT DATEDIFF_BIG(day, '2005-12-12', '2017-10-10');  
SELECT DATEDIFF_BIG(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');  
/*结果:  
4320  
4320  
1  
*/  
  
--  inputdate AT TIME ZONE timezone  #时区时间  
SELECT * FROM sys.time_zone_info -- 时区及名称参考  
SELECT CONVERT(DATETIME,'2017-10-10') AT TIME ZONE 'Pacific Standard Time'  
SELECT CONVERT(DATETIME,'2017-10-10') AT TIME ZONE 'China Standard Time'  
SELECT CONVERT(datetime2(0), '2017-10-10T01:01:00', 126) AT TIME ZONE 'Pacific Standard Time';  
SELECT CONVERT(datetime2(0), '2017-10-10T01:01:00', 126) AT TIME ZONE 'China Standard Time';  
/*结果:  
2017-10-10 00:00:00.000 -07:00  
2017-10-10 00:00:00.000 +08:00  
2017-10-10 01:01:00 -07:00  
2017-10-10 01:01:00 +08:00  
*/  
  
--  COMPRESS ( expression ) # GZIP算法压缩为varbinary(max)  
DECLARE @COM varbinary(max)  
SELECT @COM = COMPRESS(N'{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}')  
SELECT @COM  
--结果:0x1F8B08000000000004002DCC410A80300C44D17F94D2B51B85A2780E2FE042A414AAD4BA12EFEE……(略)  
  
--  DECOMPRESS ( expression )#解压缩  
SELECT CAST(DECOMPRESS(@COM) AS NVARCHAR(MAX))  
--结果:{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}  
  
  
--  SESSION_CONTEXT(N'key')  #获取指定的键的值  
EXEC sp_set_session_context 'user_id', 4;  --设置键值  
SELECT SESSION_CONTEXT(N'user_id');    
--结果:4  
  
  
--  ISJSON ( expression ) #测试字符串是否包含有效JSON  
DECLARE @param1 NVARCHAR(MAX)  
DECLARE @param2 NVARCHAR(MAX)  
SET @param1 = N' "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 '    
SET @param2 = N'[{ "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }]'    
SELECT ISJSON(@param1) as P1, ISJSON(@param2) as P2  
GO  
/*结果:  
P1  P2  
--  --  
0   1  
*/  
  
--  JSON_VALUE ( expression , path ) #从 JSON 字符串中提取值  
DECLARE @param NVARCHAR(MAX)  
SET @param = N'{ "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }'    
SELECT JSON_VALUE(@param,'$.id') as P1,JSON_VALUE(@param,'$.info.name')as P2  
GO  
/*结果:  
P1  P2  
--  ----  
2   John  
*/  
  
--  JSON_QUERY ( expression [ , path ] )  #从 JSON 字符串中提取对象或数组  
DECLARE @param NVARCHAR(MAX)  
SET @param = N'{ "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }'    
SELECT JSON_QUERY(@param,'$.info')  
GO  
--结果:{ "name": "John", "surname": "Smith" }  
  
  
--  JSON_MODIFY ( expression , path , newValue )  #更新的 JSON 字符串中属性的值并返回更新的 JSON 字符串  
DECLARE @param NVARCHAR(MAX)  
SET @param = N'{ "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }'    
SELECT JSON_MODIFY(@param,'$.info.surname','newValue')  
GO  
--结果:{ "id" : 2,"info": { "name": "John", "surname": "newValue" }, "age": 25 }  
  
  
  
/**************************************************************  
SQL Server 2017 新增的函数  
***************************************************************/  
  
--  CONCAT_WS ( separator, argument1, argument1 [, argumentN]… ) #按第一个分隔符连接后面的字符  
SELECT CONCAT_WS( ' - ', 1, 'kk', '12dd')  
--结果:1 - kk - 12dd  
  
--  TRANSLATE ( inputString, characters, translations) #整体对应替换  
SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');  
SELECT REPLACE(REPLACE(REPLACE(REPLACE('2*[3+4]/{7-2}','[','('), ']', ')'), '{', '('), '}', ')');  
SELECT TRANSLATE('2*[3+4]/[7-2]', '[2', '61');  
/*结果:  
2*(3+4)/(7-2)  
2*(3+4)/(7-2)  
1*63+4]/67-1]  
*/  
  
--  TRIM ( [ characters FROM ] string ) #删除字符串左右空格字符  
SELECT TRIM( '     test    ') AS Result,LTRIM(RTRIM('     test    '))  
  
--  STRING_AGG ( expression, separator ) #同列字符相连成一行  
SELECT STRING_AGG (MyName, CHAR(13))  FROM (VALUES('AAAA'),('BBBBB'),('CCCCCC') )AS T(MyName)  
SELECT STRING_AGG (MyName,',') FROM (VALUES('AAAA'),('BBBBB'),('CCCCCC') )AS T(MyName)  
SELECT STRING_AGG (MyName,',') WITHIN GROUP(ORDER BY id DESC ) FROM (VALUES(1,'AAAA'),(1,'BBBBB'),(2,'CCCCCC'))AS T(id,MyName)  
/*结果:  
AAAA BBBBB CCCCCC  
AAAA,BBBBB,CCCCCC  
CCCCCC,BBBBB,AAAA  
*/  

查看评论

SQL Server 2012 2016 2017 新增函数

/************************************************************** SQL Server 2012 新增的函数 **************...
  • kk185800961
  • kk185800961
  • 2017-10-10 19:39:49
  • 1502

Sql Server 2016中增加了对JSON的内置支持

在SQL Server 2016中,可以使用FOR JSON子句把数据表直接生成为JSON字符串,反过来可以使用OPENJSON来把JSON数据解析为表变量,这大大方便了在数据库中处理非结构化数据。但...
  • betreex
  • betreex
  • 2016-01-22 15:41:12
  • 1081

【SQL 编程你也行】SQL Server新功能之函数:sum函数(2005、2008、2012、2014)

sum函数在SQL Server 2005中,添加了 over(partition by )的功能。 在2012、1014中添加了 over(partition by  order by )的功能,...
  • yupeigu
  • yupeigu
  • 2016-01-14 16:02:48
  • 1795

SQL SERVER2012 新增的14个内置函数

--PARSE SELECT PARSE('WEDNESDAY,28 MARCH 2012' AS DATETIME2 USING'zh-CN') AS Result, PARSE(...
  • smahorse
  • smahorse
  • 2012-08-31 15:01:51
  • 575

WindowsServer2016上安装Sqlserver2012

被搞死了,一开始安装的时候,.NET Framework 3.5 Features没有安装,WindowsServer2016默认不安装这个。所以要一开始就把他安装上。否则安装sqlserver201...
  • mo_feng_
  • mo_feng_
  • 2017-06-15 11:44:46
  • 2606

SQL Server 2016新增:string_split函数(拆分字符串)

SQL Server 2016新增了string_split函数,专门用来拆分字符串。 之前,在开发中经常会有拆分字符串的需求,要么用xml来实现, 要么写个自定义函数来实现。 所以,期盼了很久,...
  • yupeigu
  • yupeigu
  • 2018-01-28 16:36:35
  • 452

SQL Server2012新特性:新增内置函数

序号 名称 类型 说明 MSDN 1 Concat 字符串类   点击打开链接 2 Format 字符串类   点击打开链接 3 CHOOSE 逻辑类   点击打开链接 4 IIF 逻...
  • yenange
  • yenange
  • 2017-12-14 17:40:44
  • 155

MSSQL Server Replication机制主从同步实践

MSSQL Server Replication机制主从同步实践
  • yangzhenping
  • yangzhenping
  • 2016-01-08 16:53:11
  • 2640

SQL Server 2012-2016-2017 简体中文版下载和序列号

SqlServer 2017 下载地址及密钥 下载地址: ed2k://|file|cn_sql_server_2017_developer_x64_dvd_11296175.iso|17...
  • hbtianmimi
  • hbtianmimi
  • 2013-07-23 14:18:33
  • 4952
    个人资料
    等级:
    访问量: 22万+
    积分: 2971
    排名: 1万+
    文章分类