[MSSQL2016] FORMATMESSAGE格式化訊息

MSSQL於2016版本中提供FORMATMESSAGE函數，在此僅針對訊息格式化處理進行探討，語法如下。

FORMATMESSAGE('msg_string' , [ param_value [ ,...n ] ] )

msg_string

為格式化訊息字串，最多可支援2,047字元，包含參數值(s)預留位置，超過部分將被截斷。

param_value

為訊息字串中所用之參數值，依位置依次指定，最多支援20個參數值。

FORMATMESSAGE所採用之參數用法，類似C/C++輸出格式簡化版本，但僅支援字串及整數兩大類(無浮點數)，以下相關範例及說明，以常用字串(%s)及10進制具符號整數(%d)為例。

參數	說明
%s	字串
%d	10進制具符號(正負號)整數
%u	10進制無符號整數
%o	8進制無符號整數
%x	16進制無符號整數

FORMATMESSAGE基本用法是將訊息字串依序置換參數值方式組合輸出字串，另外尚可搭配其他額外參數（類似C/C++）以長度限定、字串填補及方向(左或右側)等擴展字串處理能力。

填補功能-長度限定

可於"%"和參數間額外增加數字以控制其輸出長度，如%5s代表當輸出長度小於5位之字串時，將於左方以空白字進行補足5碼，尚可控制輸出之最大長度（強制截斷），應用及對應參數整理如下表。

#	長度參數 (%與字母間)	意義	說明
1	整數   (ex: %5s) (字串/數字均支援)	最小長度(m)	如長度<m時，以空白填補至最小長度(m)
2	浮點數 (ex: %5.8s) (不支援數字)	最小長度.最大長度(m.n)	如長度<m時，以空白填補至最小長度(m) 如長度>n時，則超出最大長度(n)部分將截斷

當長度不足時，預設以空白字元進行填補(左方)，也可改用"0"進行填補，則需於長度限定參數前額外增加一個"0"即可，如%05d代表當輸出小於5位之數字時，將於前方(左側)補0，使其總長度度為5位。

填補功能-填補/對齊方向

另外，可控制輸出以左側(右方填補)或右側(左方填補，預設)對齊，如欲變更為右方填補時，則在"%"和字母間額外加入負號(-)即可。如%-8d，代表輸出長度8整數置左對齊。

類型	SQL	[輸出]
數字	SELECT Val , FORMATMESSAGE('Val: %d', Val) "Val: %d"  , FORMATMESSAGE('[%5d]',   Val) "[%5d]"  , FORMATMESSAGE('[%05d]',  Val) "[%05d]"  , FORMATMESSAGE('[%-05d]', Val) "[%-05d]"  , FORMATMESSAGE('[%5.5d]', Val) "[%5.5d]" FROM (VALUES (123) , (12345) , (123456)) AS Test(Val)
字串	SELECT Val  , FORMATMESSAGE('Val: %s',  Val) "Val: %s"  , FORMATMESSAGE('[%5s]',    Val) "[%5s]"  , FORMATMESSAGE('[%05s]',   Val) "[%05s]"  , FORMATMESSAGE('[%-05s]',  Val) "[%-05s]"  , FORMATMESSAGE('[%5.5s]',  Val) "[%5.5s]" FROM (VALUES ('ABC') , ('ABCDE') , ('ABCDEF')) AS Test(Val)

範例中各欄位說明如下:

範例	字串	數字	說明
直接輸出	%s	%d	直接置換輸出
右補空白至長度5	%5s	%5d	置左，右補空白至長度5，如超過仍完整輸出
左補空白至長度5	%-5s	%-5d	置右，左補空白至長度5，如超過仍完整輸出
右補空白至長度5(m) 如超過5(n)則取5(n)	%5.5s	%5.5d	置左，右補空白至長度5，如已超過5(n)，以%s則取5碼(n)其餘截斷，但%d仍將完整輸出。

FORMATMESSAGE格式化訊息(字串)函數大幅提升MSSQL字串輸出處理能力，簡化過去需用多函數組建而成之功能，如下範例。

SELECT *

  , REPLACE(FORMATMESSAGE('[%-5s%05d%-5.5s]', Name, Vote, Commets), ' ','　' ) OUTPUT

FROM (VALUES ('閃電麥坤', 1912, '洲際盃')

, ('拖線', 945, '住在油車水鎮')) AS iVoting(Name, Vote, Commets)

[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)