As the name suggests, it concatenates strings. In earlier versions of Sql Server we have the option of performing concatenation using the '+' symbol. But the overhead was that if the types that are participating in the concatenation are not of varchar type, then we had to do explicit conversion else it was resulting in error. However, the new Concat() function takes care of this explicit conversion.
Purpose: Concatenates variable number of string arguments and returns a single string.
Syntax: Concat( Value1,Value2,...,ValueN)
Example1: Simple Concat
Select Concat('Hello',' ' ,'Concat') As [Concat]
/* Output
Concat
--------
Hello Concat
*/
Example 2: Concat string with integer
Select Concat('String',10) As [Concat]
/* Output
Concat
-------
String10
*/
Example 3: Concat with multiple datatypes
Select Concat('Sql',11, Null, 'Code Name', 'Denali', 'CTP' ,3) As [MultipleField Concat]
/* Output
MultipleField Concat
---------------------
Sql11Code NameDenaliCTP3
*/
The null is converted to empty string. Where as the traditional '+' symbol would have return null
Select 'Sql' + CAST(11 as varchar(10)) + Null + 'Code Name Denali CTP 3' As [MultipleField Concat]
/* Output
MultipleField Concat
---------------------
NULL
*/
We would have to use either ISNull Or Coalesce to get the desired result as
Select 'Sql' + CAST(11 as varchar(10)) + Coalesce(Null,'') + 'Code Name Denali CTP 3' As [MultipleField Concat]
--OR
Select 'Sql' + CAST(11 as varchar(10)) + IsNull(Null,'') + 'Code Name Denali CTP 3' As [MultipleField Concat]
/* Output
MultipleField Concat
---------------------
Sql11Code Name Denali CTP 3
*/
Example 4: Concat with table columns
Declare @t table(FirstName varchar(10),LastNAme varchar(10))
Insert into @t select 'Niladri','Biswas' Union All Select 'Deepak','Goyal'
Select FullName = Concat(Concat(FirstName,' ' ),LastName) from @t
/* Output
FullName
---------
Niladri Biswas
Deepak Goyal
*/
In this case we have seen how Concat can be use in conjunction with table columns as well as it's nesting.
Example 5: Concat with one argument
Select Concat('Single argument') As [Concat]
Error
Msg 189, Level 15,State 1,Line 1 The concat function requires 2 to 254 arguments
Hope this helps