Why should I use CONCAT when SQL Server can concatenate using + ?

Recently, I wrote a blog on CONCAT string function in 2012 and there were couple of interesting questions about the CONCAT function. I shall answer those questions in this blog, as it would be better to answer with examples.

The first question we will look to answer is, Why should we I use CONCAT function when SQL Server can concatenate using +?

When CONCAT is used, all values passed into the function are implicitly converted to String values and then concatenated and NULL values are implicitly converted into am empty string. To better understand this, let us look at an example.

Use the following code to create a table, note that this is similar to the code used in the actual blog, except the values passed into Address1 in the first 2 rows is NULL.

CREATE TABLE [dbo].[Customer_Address_New](
 [CustomerID] [bigint] NULL,
 [Address1] [varchar](50) NULL,
 [Address2] [varchar](50) NULL,
 [ZipCode1] [char](5) NULL,
 [ZipCode2] [char](4) NULL

INSERT INTO Customer_Address_New
(1,NULL,'Moon Walk Drive', 24578,2881 ),
(2,NULL,'Roof Top Lane', 54856,5421 ),
(3,'#3','Full Thottle Blvd', 90425,5782 ),
(4,'#4','Drive Slow Road', 18854,6502 )

Once you inserted the data, run the following 2 statements to and look at the results

SELECT CustomerID, CONCAT(Address1,' ',Address2) AS Address,
CONCAT(ZipCode1,'-',ZipCode2) AS ZIP
FROM Customer_Address_New
SELECT CustomerID, Address1 + ' ' + Address2 AS Address,
ZipCode1 + '-' + ZipCode2 AS ZIP
FROM Customer_Address_New

The output will look like this..

Note that the Address column is the result set from using +. By default SQL Server returns a NULL when you concatenate NULLs and Strings. You have to turn on the option SET CONCAT_NULL_YIELDS_NULL OFF and run the queries using + to get the desired results  as shown below.

Apart from the benefit of concatenating NULL values, CONCAT provides another benefit by implicitly converting every argument as string value, so it makes easy to get the output as expected without much fuss (I mean using CAST or CONVERT string functions). To look at this, let me give a very simple example. Run the following code.


SET @INTI = 1234
SET @STRINGDATE = '10/10/2011'

SELECT  @INTI + ' ' +@DATE + ' ' +  @STRINGDATE 


You will see the following output.

If you see the results, the output we got when concatenated using +, is not we were looking for, in order to get the output returned by CONCAT, we have to use CAST and Convert functions to first convert the integer value 1234 to a string and so on, which is out of topic for this blog post.

Finally, to answer one more question on performance benefit of CONCAT, I haven’t yet tested the performance benefits of using CONCAT in SQL Server 2012 over “+”, so I cannot comment on it..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Publishing 100th Blog on LearnSQLWithBru

Today, I am happy to share that I am writing my 100th blog on www.LearnSQLWithBru.com. On this note, I would like to personally thank all the readers who have been visiting this blog. I would also like to thank my wife Tripura Medishetty, who has always taken care of me and without her support, I would not have dedicated a ton of hours towards my blog and this website.

My primary goal in writing these blogs, is to share what I continuously learn and help readers to learn at least one single topic in SQL Server by reading my blog posts. I hope I have not disappointed my readers. Having said that I am looking forward to write more often in the future and cover as many areas and topics as possible on SQL Server. If you have been following this site, we recently started a new section called Learn SQL Server, which will focus on teaching SQL Server to first time users.

Apart from writing regular blog posts on this site, I am also planning to write more beginner topics as part of our new section Learn SQL Server for Beginners. Re-start the daily SQL dose, (SQL Tidbits, from Jan 1st 2012) and also expand sections under Learn SQL Server, for Mid-level and Advanced SQL users and so on……

As always, I seek your feedback and comments on what new sections / areas can make this site better.. 

— Bru Medishetty