Database Simplified Headline Animator

Database Simplified

Friday, 23 September 2011

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>

No comments:

Post a comment