Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Concatenate SQL Server Columns into a String with CONCAT()


By:   |   Read Comments (16)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Functions - System

SQL Server Indexing Tips and Tricks --- Free MSSQLTips Webcast!


Problem

I need to produce mailing labels from my SQL Server database so I am using the + sign for concatenation the first, middle, and last names together. The issue I see is I get NULL for a lot of rows. This makes me unable to produce the full names.  What are some options to address this problem?  Check out this tip to learn more about concatenating data in SQL Server.

Solution

Prior to SQL Server 2012 concatenation was accomplished by using the plus (+) sign to concatenate fields together. The limitation of this method is if any of the fields you are concatenating are NULL, the entire result is NULL. With the introduction of SQL Server 2012 there is a new CONCAT() function that replaces NULL with an empty string. Take a look at this tip to see how this new function works and how it can be beneficial in your code.

For this demo I am going to use the Person.Person table from the AdventureWorks2012 database to demo having to generate a full name for creating mailing labels. First, the code below is the old technique to perform concatenation using the + sign:

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    Title+ ' ' + FirstName + ' ' + MiddleName + ' ' + LastName as MailingName
FROM Person.Person

As you can see in the screen shot below the MailingName is NULL for any row that has NULL for any one of the name columns. The only rows that have MailingName filled in have a value for all the title, firstname, middlename, and lastname columns. This could be corrected by wrapping ISNULL(column,'') around all the columns in the concatenated field to account for any values having nulls, but that code gets long, messy, and hard to read.

Concat the old way

Below is an example is using ISNULL along with the plus sign for concatenation. The ISNULL function will replace null values with the value noted in the second parameter, which in this example is an empty string.

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    ISNULL(Title,'') + ' ' + ISNULL(FirstName,'') + ' ' 
  + ISNULL(MiddleName,'') + ' ' + ISNULL(LastName,'') as MailingName
FROM Person.Person

As you can see in the example below, the MailingName is no longer NULL as it replaced the NULL values with an empty string. This achieves the same as using the CONCAT() function, but requires a lot more code and readability.

Concat with ISNULL

The next set of code is using the new CONCAT() function, new to SQL Server 2012. It replaces NULL values with an empty string of type VARCHAR(1). This code is much easier to read and write when you need to have NULL code handling in place.

SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    CONCAT(Title,' ',FirstName,' ',MiddleName,' ',LastName) as MailingName
FROM Person.Person

If you see the results of this, all MailingName values are present, even if they have some of the columns set to NULL.

CONCAT the new way

As you can see this new function is very handy and behaves much different that the old form of concatenation. Instead of evaluating to NULL if any if the columns contain NULL values, the CONCAT() function replaces it with an empty string. This is very useful for coding around NULL values.

Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Send me SQL tips:

    



Wednesday, March 11, 2015 - 12:03:28 PM - Bob Elander Back To Top

Came in of the Bing and you have provided a valuable solution.  I have a question though I need to combing 2 text fields and a Boolean field as well and am receiving an error. 


Tuesday, March 10, 2015 - 4:33:15 PM - Jeff Page Back To Top

Just what I was looking for - thanks, Chad!


Saturday, November 29, 2014 - 2:53:04 PM - Athar Anis Back To Top

I spent several hours trying to figure out why '+' would return NULL as a result. Your article answered it and was up to the point and highly informative.

Thanks


Wednesday, June 25, 2014 - 1:19:14 PM - Brian Nordberg Back To Top

Don't forget SET CONCAT_NULL_YIELDS_NULL { ON | OFF }  for those w/o concat function.


Friday, February 14, 2014 - 11:02:08 AM - Terry Carnes Back To Top

Great extra tip, Phil! That was exactly the question I was asking myself while reading Chad's article. Thank you for addressing it!


Friday, August 30, 2013 - 5:00:07 AM - Madivalappa Patil Back To Top

This is really very nice article.I appreciate your work.

Thank you very much!!


Thursday, August 22, 2013 - 12:42:11 PM - Phil McDermott Back To Top

Nice tip! One recommendation: if you include the spacing with the column rather than as a separate item to concatenate, it removes the leading and extra spaces.

SELECT Title, FirstName, MiddleName, LastName,
CONCAT(Title,' ', FirstName,' ', MiddleName,' ', LastName) as MailingName_HasExtraSpaces
CONCAT(Title + ' ', FirstName + ' ', MiddleName + ' ', LastName) as MailingName_NoExtraSpaces,
FROM Person.Person


Thursday, August 22, 2013 - 9:34:27 AM - Jason W Back To Top

I would think that you would wrap the CONCAT function or the + version with an LTRIM so that when there is no title you are not left with a space for the 1st character. Of course a space at the front of a name for a mailing label might not make that much of a difference.

I think one of the better features of CONCAT is that you don't need to use cast or convert to combine certiain data types.


Thursday, August 22, 2013 - 9:08:50 AM - Owen Overstreet Back To Top

Great Tip, thank you!


Wednesday, July 24, 2013 - 7:28:45 AM - Awadallah M. Ahmed Back To Top

Ok! Thank Mr Chad Churchwell .. 




Tuesday, July 23, 2013 - 5:21:30 PM - Jeff Moden Back To Top

Nice article.  I do have a question, though.  There have been many new features (FORMAT for dates, for example) that actually turn out to be quite aa bit slower than the original brute force methods.  Have you tested to see what the performance and resource usage differencess are between CONCAT and the brute force method on, say, a million rows?


Tuesday, July 23, 2013 - 3:44:56 PM - Chad Churchwell Back To Top

Thanks Frank, I appreciate the feedback.


Tuesday, July 23, 2013 - 2:49:31 PM - Frank Djabanor Back To Top

Excellent tip Chad...Another nugget to add to my SQL Server skillset. BTW, great lecture on FileStream & FileTable at JSSUG last week. Thank you.


Tuesday, July 23, 2013 - 9:09:08 AM - Chad Churchwell Back To Top

When using LTRIM if any of the fields are NULL the whole concatenated string returns NULL.  All LTRIM does is remove leading spaces, really has nothing to do with concatenation. 


Tuesday, July 23, 2013 - 7:39:19 AM - Awadallah M. Ahmed Back To Top

You can use Ltrim instead of Concat.


Monday, July 22, 2013 - 9:09:08 PM - RLF Back To Top

BIG time saver...

Nice and simple examples...


Learn more about SQL Server tools