Introduction to new CONCAT() function in SQL Server 2012

SQL Server 2012 comes with some new string functions and one of them is CONCAT. In one of my project, I had a requirement to combine some columns and display it as single column in the application. Very simple requirement right, But wait why I used CONCAT function? I can use “+” operator to concat columns!! We’ll see it in detail in this blog post. Well, this blog post is also inspired by one of the [question] asked in Stackoverflow.com.

All right, CONCAT function helps to combine two or more column’s value into a single string value. The basic syntax is –

CONCAT (string_value1, string_value2 [, string_valueN ])

And below script shows the use of the function –

SELECT FirstName ,
LastName ,
Age ,
CONCAT(FirstName + ' ', LastName + ' ', Age, ' Years Old')
FROM dbo.Student;

Concat

CONCAT function takes n number of parameters and combines them into single string. But it requires a minimum of two input values otherwise it throws an error as shown below.

Concat_With_Error

Though, you can use both “+” operator and CONCAT Function in sql server 2012 and above, but then what is the difference between the two ? Here we go by example.

⇒ Handling of NULL:

When you use NULL or any column value is null then “+” operator will make whole result NULL. On the other hand CONCAT function treats NULL as an empty string. Let’s see in below script.

SELECT 'Hello' + NULL + ' World!' AS WithNULL;

SELECT CONCAT ('Hello', NULL, ' World!') AS WithConcat;

SELECT FirstName ,
LastName ,
Age ,
CONCAT(FirstName + ' ', LastName + ' ', Age, ' Years Old')
FROM dbo.Student;

Concat_With_NULL

⇒ Handling of Datatype Conversion:

What if you want to concatenate more than one column of different datatype. For exa. You want to concat INT and String value together. See the below script. When you run it using “+” operator, it throws an error as shown below.

SELECT Firstname + ' is '
+ Age + ' years old' FROM dbo.Student;

Using_Plus_Operator

Now, to resolve that error we need to do explicit conversion as shown in below script.

SELECT Firstname + ' is ' +
CONVERT(VARCHAR, Age) + ' years old' AS ExplicitConversion
FROM dbo.Student;

Explicit_Conversion

Now let’s do the same thing using CONCAT function –

SELECT CONCAT (Firstname, ' is ', Age, ' years old') AS UsingConcat
FROM dbo.Student;  

Concat_Implicit_Conversion

No error!! Well, CONCAT() Function does an implicit conversion to string datatype for all arguments passed in the function. One more thing, in SQL Server 2012 and above you can use both CONCAT() Function and “+” operator. But the CONCAT() Function is recommended. Why? Because its syntax is easy to follow and your code looks neat and cleaner.

That’s it for now. Hope you enjoyed this post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s