SQL Server ->> CONCAT函數

標籤：

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

Msg 257, Level 16, State 3, Line 35Implicit 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_fldFROM dbo.Numbers )SELECT CONCAT(int_fld,str_fld,flt_fld,NULL_FLD, dt_fld)FROM  T

 

SQL Server ->> CONCAT函數