SQL Server 2012/2016/2017 新增函數(shù)

/**************************************************************??
SQL?Server?2012?新增的函數(shù)??
***************************************************************/??
??
--??CONCAT?(?string_value1,?string_value2?[,?string_valueN?]?)?#字符串相連??
SELECT?CONCAT('A','BB','CCC','DDDD')??
--結果：ABBCCCDDDD??
??
--??PARSE?(?string_value?AS?data_type?[?USING?culture?]?)?#轉換為所請求的數(shù)據(jù)類型的表達式的結果??
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?僅用于從字符串轉換為日期/時間和數(shù)字類型)??
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??
??
??
--??排名函數(shù)！??
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'?--同排名的后面排名連續(xù)??
,RANK??(?)?OVER?(PARTITION?BY?MyName?ORDER?BY?Num)?AS?'RANK'????????????--同排名的后面排名不連續(xù)??
,NTILE?(2)?OVER?(PARTITION?BY?MyName?ORDER?BY?Num)?AS?'NTILE'???????????--按總數(shù)分兩組，順序排名??
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??
*/??
??
??
--??分析函數(shù)！??
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'?--相對排名,排名分數(shù)參考?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'?--連續(xù)分布計算百分位數(shù)??
,PERCENTILE_DISC(0.5)WITHIN?GROUP?(ORDER?BY?Num)?OVER?(PARTITION?BY?MyName)?AS?'PERCENTILE_DISC'?--離散分布計算百分位數(shù)??
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?新增的函數(shù)??
***************************************************************/??
??
--貌似沒有什么??
??
/**************************************************************??
SQL?Server?2016?新增的函數(shù)??
***************************************************************/??
??
--??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?)?#日期之間的計數(shù)??
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??#時區(qū)時間??
SELECT?*?FROM?sys.time_zone_info?--?時區(qū)及名稱參考??
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?字符串中提取對象或數(shù)組??
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?新增的函數(shù)??
***************************************************************/??
??
--??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??
*/