/**************************************************************
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', '');
/*结果：
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
*/