SQL Server 的 CONCAT函數

這是一個SQL Server 2012後引進的新函數。作用就如同它名字的意思。它對NULL值得處理是空字符串。當然它能做的不僅是對字符的支持。它支持N個列輸入，列的類型支持更加完善。不過其實它的原理不過是把傳入的數據用CONVERT函數轉換成NVARCHAR類型。不信你傳個XML或者SQL_VARIANT類型的數據進去，你會看到馬上報錯。錯誤提示是：

Msg 257, Level 16, State 3, Line 35
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.

至少我測試結果顯示它對INT, FLOAT, DATETIME, NVARCHAR都是支持的。

測試腳本

WITH T AS 
(
SELECT TOP 10 
CAST(Num AS INT) AS int_fld,
CAST(Num AS NVARCHAR) AS str_fld,
CAST(Num AS FLOAT) AS flt_fld,
CAST(NULL AS NVARCHAR) AS NULL_FLD,
CAST(Num AS datetime) AS dt_fld
--CAST(Num AS sql_variant) AS var_fld,
--CAST('<a>aa</a>'AS xml) AS xml_fld
FROM dbo.Numbers 
)

SELECT CONCAT(int_fld,str_fld,flt_fld,NULL_FLD, dt_fld)
FROM  T