CONCAT is the new T-SQL function introduced in SQL Server 2012. It is used to concatenate the values. It accepts many parameter values seperated by comma. All parameter values are concatenated to a single string.
A simple example is
SELECT CONCAT('SQL Server',' 2012')
which results to SQL Server 2012
The same can be done using + in the earlier versions
SELECT 'SQL Server'+' 2012'
which results to SQL Server 2012
But did you know the advantage of CONCAT function over +?
SELECT 'SQL Server'+' 2012'+NULL
When you execute the above, the result is NULL
But the CONCAT function will simply ignore NULL values
SELECT CONCAT('SQL Server',' 2012',NULL)
The result is SQL Server 2012
So by using CONCAT function, you do not need to worry about handling NULL values.
How many of you know this?
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
Thank you sir……..nice information………..
I am glad that you liked it ssn.
I think the result is missing in this blog somehow.
Kaushik – which result?
Not only concat but also with sum and many other functions. Right Pinal Sir??
Yes SUM and COUNT also ignore NULL values but with warning
Thank you sir….
I regularly used to refer your articles about SQL Server and you’re Simply awesome.
shyamknaidu – Thanks!
Thank sir! I had to use sub-query to avoid null value.
great. thanks for sharing Anil
using 2012 sql server. need to concate values with delimiter (values will come from SSRS – 1 single parameter with multiple values)
concat(NULL, “something”) return null.
Take care about this guys.
Hi
If I have a List (@v1,@v2,@v3,…..)
Is it possible some like this:
CONCAT(@v1,@v2,@v3,…..)=NULL if some variable is null.
I need this way because the list it’s in a dynamic SQL.