XML FeedsSQL Server Denali CTP3 brings a couple of new functions, one of these is the CONCAT function. The CONCAT function returns a string that is the result of concatenating two or more string values.
The syntax of the CONCAT function looks like this
CONCAT ( string_value1, string_value2 [, string_valueN ] )
You can concatenate between 2 and 254 values, if you use for example only one value, you will get an error
- select CONCAT (1)
Msg 189, Level 15, State 1, Line 1
The concat function requires 2 to 254 arguments.
Here is some additional information
CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, then an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions
Let's run some code and do some comparison with a regular string concatenation by using the @val + @val2
- declare @i char(1) ='1'
- declare @i3 char(1) ='3'
- select CONCAT (@i,@i3)
- select @i+ @i3
------
13
13
As you can see both of these return the value 13
What happens if one of the data type is an integer?
- declare @i char(1) ='1'
- declare @i3 int ='3'
- select CONCAT(@i,@i3)
- select @i+ @i3
-----------------
13
4
As you can see CONCAT concatenates the values while the other method does arithmetic and adds the values since one of them is an integer
Here is another example that does the same
- declare @i char(1) ='1'
- declare @i2 int =2
- declare @i3 char(1) ='3'
- select CONCAT(@i,@i2,@i3)
- select @i+ @i2+ @i3
-----------------
123
6
What happens if one of the values is NULL?
- declare @i char(1) ='1'
- declare @i2 int =null
- declare @i3 char(1) ='3'
- select CONCAT(@i,@i2,@i3)
- select @i+ @i2+ @i3
--------------
13
null
As you can see the CONCAT functions makes the NULL an empty string while the other method does not.
In order to get the same output, the old method is a lot more code
- declare @i char(1) ='1'
- declare @i2 int =null
- declare @i3 char(1) ='3'
- select CONCAT(@i,@i2,@i3)
- select isnull(@i,'')+ isnull(convert(varchar(10),@i2),'')+ isnull(@i3,'')
-----
13
13
So what is your opinion, are you happy that the CONCAT function has been added to SQL Server?
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
/me is happy ! 
Does anyone else notice the BOL for this function has an example that is incorrect. 
awesome. now you can jsut write concat(column1,column2) instead of cast(column1 as varchar(50)) + column2 
That is correct, no spaces, BOL is incorrect 
Nice, but what about GROUP_CONCAT? 
it would be better if you had a couple of options...
I love that this function is being added. Of course I probably won't be working with this version for 3 years or more. 
Great new feature. The implicit conversion is a huge win.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.