Let's learn Concat Function in Sql Server 2012

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

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.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
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
http://www.dotnetfunda.com
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)