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