Let's learn Concat Function in Sql Server 2012

Posted by in Sql Server category on for Beginner level | Points: 100 | Views : 3351 red flag
Rating: 3 out of 5  
 1 vote(s)

In this article, we will learn Concat Function in Sql Server 2012


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.


Concatenates variable number of string arguments and returns a single string.


Concat( Value1,Value2,...,ValueN) 

Example1: Simple Concat

Select Concat('Hello',' ' ,'Concat') As [Concat]

/* Output

Hello Concat


Example 2: Concat string with integer

Select Concat('String',10) As [Concat]

/* Output

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

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

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.


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.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)