In this article, we will learn Concat Function in Sql Server 2012
Introduction
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
*/
Example 4: 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
*/
Example 5: 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.
Conclusion
SQL Server 2012 (Denali) seems to be very mature and promising and has embedded with many new functions.In this article we have looked into the Concat function and it's usefulness into the T-Sql programming parlance.Hope the article will be useful.