[MSSQL]2012~2017版本重要新函數

MSSQL 2012~2017版本重要新函數

	Funcation / Object	說明	範例
物件	SEQUENCE [2012]	SEQUENCE為依自定義可產生一連串序列數值之物件。 與ORACLE之SEQUENCE物件雷同
	DROP IF EXISTS [2016]	簡化DROP TABLE作法	DROP TABLE IF EXISTS #Notify;
	JSON [2016]	ü   FOR JSON子句可將結果集格式化為JSON。 ü   OPENJSON資料列集函數可將JSON文字轉換成一組資料列和資料行。
轉換	PARSE [2012]	轉換成指定之資料類型。(可指定culture) PARSE(<str> AS data_type [USING culture ])	SELECT PARSE('29/02/2020' AS datetime                USING 'en-GB')--成功      , PARSE('29/02/2020' AS datetime)--錯誤
	TRY_PARSE [2012]	嘗試轉換至指定資料型態，失敗回傳NULL。僅適用字串轉換為日期/時間及數值類型。(可指定culture) TRY_PARSE(<str> AS data_type [USING culture ])	SELECT TRY_PARSE('29/02/2020' AS datetime USING 'en-GB') --成功      , TRY_PARSE('29/02/2020' AS datetime)               --NULL
	TRY_CONVERT [2012]	嘗試轉換至指定資料型態，失敗回傳NULL。 TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )	SELECT TRY_CONVERT(datetime, '02/29/2020') --成功      , TRY_CONVERT(datetime, '29/02/2020') --NULL
日期	DATEFROMPARTS (尚有許多類似指令) [2012]	傳回對應至指定年份、月份和日期值的date值。 DATEFROMPARTS ( year, month, day )	SELECT DATEFROMPARTS (2020, 2, 29)
	EOMONTH [2012]	取得月底日期	SELECT EOMONTH('2020-02-15')
	DATEDIFF_BIG [2016]	使用方法與原DATEDIFF函數相同，解決溢位問題。	SELECT DATEDIFF_BIG(SS, 0, GETDATE()) (使用DATEDIFF則發生溢位錯誤)
邏輯	CHOOSE [2012]	傳回後方清單中指定位置之資料。	SELECT CHOOSE(2, 'A', 'B', 'C')
	IIF [2012]	據布林運算式結果 (true/false)傳回兩個參數中。 類似EXCEL之IF指令。	SELECT IIF(1=0, 'TRUE', 'FALSE')
字串 函數	CONCAT [2012]	串連多個字串資料。(如數值會自動轉型) (如需加上分隔符號，則可改用CONCAT_WS)。	SELECT CONCAT('A', NULL, 'B', 3)
	FORMAT [2012]	傳回以指定格式(選擇性文化)特性所格式化的值。 FORMAT(value, [,culture])	SELECT FORMAT(GETDATE(), 'HHmmss')
	STRING_SPLIT [2016]	依分隔符號字元將字串分割成資料列。	SELECT value --欄位名稱為value FROM STRING_SPLIT('A,B,C', ',')
	CONCAT_WS [2017]	多個字串以分隔符號串連。(如數值會自動轉型) CONCAT_WS('delimiter', arg1, arg2 [,argN])	SELECT CONCAT_WS(',', 'A', 'B', 'C')
	TRANSLATE [2017]	字串以指定對應方式進行字元置換 與ORACLE近似（略不同）	SELECT TRANSLATE(N'１９５號'                  , '１２３４５６７８９'                  , '123456789')
	TRIM (增加FROM) [2017]	移除字串前後的空白字元或其他指定字元 TRIM([char FROM] )	SELECT TRIM(',' FROM 'A,B,C,')
	STRING_AGG (字串彙總函數) [2017]	將字串資料列以分隔符號進行彙總運算 STRING_AGG(expr , 'delimiter') WITHIN GROUP (ORDER BY <order_by_expr>) 與STRING_SPLIT互為反向應用	SELECT STRING_AGG(VAL, ',')         WITHIN GROUP (ORDER BY VAL) FROM (VALUES('A'), ('B'), ('C')) AS TEST(VAL)
分析 函數	FIRST_VALUE [2012]	取得群組中第一筆資料，依運算式計算並傳回結果。 FIRST_VALUE | LAST_VALUE ( expression ) OVER ( [PARTITION BY ] ORDER BY <order_list>  [rows_range_clause ] )	SELECT FIRST_VALUE(VAL) OVER(ORDER BY VAL)      , LAST_VALUE(VAL) OVER(ORDER BY VAL DESC)             , LAST_VALUE(VAL) OVER(ORDER BY VAL) FROM (VALUES('A'), ('B'), ('C')) AS TEST(VAL)
	LAST_VALUE [2012]	取得群組中最後一筆記錄之運算結果。
	LEAD [2012]	LEAD/LAG: 在資料集中取得相對於目前資料列在指定位移位置（t±N）之資料列。	SELECT VAL , LEAD(VAL, 1) OVER(ORDER BY VAL) FROM (VALUES('A'), ('B'), ('C')) AS TEST(VAL)
	LAG [2012]	LEAD/LAG: 在資料集中取得相對於目前資料列在指定位移位置（t±N）之資料列。	SELECT VAL , LAG(VAL, 1) OVER(ORDER BY VAL) FROM (VALUES('A'), ('B'), ('C')) AS TEST(VAL)
	PERCENT_RANK [2012]	計算同一群組中資料，每筆資料在群組中之排名。 (轉換為百分率，值域為0.0~1.0)
	CUME_DIST [2012]	計算同一群組中資料，每筆資料在群組中之累計排名。 (值域為0.0~1.0)
	PERCENTILE_DISC [2012]	依據資料值的離散分佈特性以計算百分位數，其回傳結果為是現存在特定值。
	PERCENTILE_CONT [2012]	依據資料行值的連續分佈以計算百分位數，回傳計算值則是以內插值(interpolated)來取代，可能不等於群組中的任何特定值。	SELECT VAL , CUME_DIST() OVER(ORDER BY VAL) , PERCENT_RANK() OVER(ORDER BY VAL) , PERCENTILE_DISC(0.3) WITHIN GROUP(ORDER BY VAL) OVER() , PERCENTILE_CONT(0.3) WITHIN GROUP(ORDER BY VAL) OVER() FROM (VALUES(1), (2), (3), (4)) AS TEST(VAL)