SQL QnA: [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)為例。

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

填補功能-長度限定

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

#	長度參數 (%與字母間)	意義	說明
1	整數 (ex: %5s) (字串/數字均支援)	最小長度(m)	如長度<m時，以空白填補至最小長度(m)
2	浮點數 (ex: %*5.8*s) (不支援數字)	最小長度.最大長度(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)

SQL QnA