CONCAT() In SQL Server 2011 ("Code Named Denali")

SQL Server 2011 has come with new function CONCAT() with return string after concatenating 2 or more values.

Syntax :

CONCAT ( values1,Value2,Value3….Upto 254 Values)

CONCAT takes Minimum 2 input values and 254 Maximum input Values, In Simple word you can concatenate at least 2 values and at most 254 values.

Let’s Explore :

Select CONCAT('Microsoft','SQL','Server','2011') As Value

Output :

Value
----------------------
MicrosoftSQLServer2011

Concatenate Integer Type With String

Select CONCAT('SQL Server',2011) As Value

Output :

Value
----------------------
SQL Server2011

Concatenate Integer, Date And String

Select CONCAT(GETDATE(),' Is Date Of Year', 2011) As Value

Output :

Value
--------------------------------------------------------------------
Sep 24 2011 11:42AM Is Date Of Year2011

Concatenate Null Value With Any Data type

Select CONCAT('ADDED', NULL)

Output :

Value
-----
ADDED

Concatenate Integer With Integer

Select CONCAT(1,1,1,1) As Value

Output :

Value
------------------------------------------------
1111

So The Beauty Of CONCAT() function is that you need not to convert any value explicitly into string to get result everything is automatically handled by CONCAT() Function. however CONCAT Function Doesn’t Concatenate XML datatype. you need to convert xml data type to varchar explicitly then only you can concatenate xml data type.

Declare @XML1 XML
Declare @XML2 XML
Set @XML1='<table><data>1</data></table>'
Set @XML2='<table><data>1</data></table>'
Select Concat(@XML1,@XML1)

Output :

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

Now Try Following

Declare @XML1 XML
Declare @XML2 XML
Set @XML1='<table><data>1</data></table>'
Set @XML2='<table><data>1</data></table>'
Select Concat(Convert(Varchar(100),@XML1),Convert(Varchar(100),@XML2))

Output :

Value
----------------------------------------------------------
<table><data>1</data></table><table><data>1</data></table>