2017 新增函數(shù)

/**************************************************************
SQL Server 2012 新增的函數(shù)
***************************************************************/
-- CONCAT ( string_value1, string_value2 [, string_valueN ] ) #字符串相連
SELECT CONCAT('A','BB','CCC','DDDD')
--結(jié)果：ABBCCCDDDD
-- PARSE ( string_value AS data_type [ USING culture ] ) #轉(zhuǎn)換為所請求的數(shù)據(jù)類型的表達式的結(jié)果
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;
/*結(jié)果：
2010-12-13 00:00:00.0000000
345.98
2010-12-16 00:00:00.0000000
*/
-- TRY_CAST 、TRY_CONVERT、TRY_PARSE (TRY_PARSE 僅用于從字符串轉(zhuǎn)換為日期/時間和數(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)
/*結(jié)果：
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;
--結(jié)果：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;
--結(jié)果：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',',')
/*結(jié)果：
value
-----
A
B
C
*/
-- STRING_ESCAPE( text , type ) #特殊字符轉(zhuǎn)成帶有轉(zhuǎn)義字符的文本（type只支持json）
SELECT STRING_ESCAPE('\ / \\ " ', 'json') AS escapedText;
--結(jié)果：\\ \/ \\\\ \"
-- 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');
/*結(jié)果：
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';
/*結(jié)果：
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
--結(jié)果：0x1F8B08000000000004002DCC410A80300C44D17F94D2B51B85A2780E2FE042A414AAD4BA12EFEE……（略）
-- DECOMPRESS ( expression )#解壓縮
SELECT CAST(DECOMPRESS(@COM) AS NVARCHAR(MAX))
--結(jié)果：{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}
-- SESSION_CONTEXT(N'key') #獲取指定的鍵的值
EXEC sp_set_session_context 'user_id', 4; --設(shè)置鍵值
SELECT SESSION_CONTEXT(N'user_id');
--結(jié)果：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
/*結(jié)果：
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
/*結(jié)果：
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
--結(jié)果：{ "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
--結(jié)果：{ "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')
--結(jié)果：1 - kk - 12dd
-- TRANSLATE ( inputString, characters, translations) #整體對應(yīng)替換
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', '');
/*結(jié)果：
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)
/*結(jié)果：
AAAA BBBBB CCCCCC
AAAA,BBBBB,CCCCCC
CCCCCC,BBBBB,AAAA
*/

posted @ 2023-01-02 14:38 熊大熊二閱讀(164) 評論(0) 收藏舉報

刷新頁面返回頂部

記性不太好,特別需要這么一個博客!QQ:952309057

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

公告