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 –
And below script shows the use of the function –
SELECT FirstName , LastName , Age , CONCAT(FirstName + ' ', LastName + ' ', Age, ' Years Old') FROM dbo.Student;
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.
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;
⇒ 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;
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;
Now let’s do the same thing using CONCAT function –
SELECT CONCAT (Firstname, ' is ', Age, ' years old') AS UsingConcat FROM dbo.Student;
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.