Concatenate SQL Server Column Values with CONCAT()

Problem

I need to produce mailing labels from my Microsoft SQL Server database. Currently, I am using the + sign to concatenate 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 concatenate SQL Server column values? Check out this tutorial to learn more about concatenating columns in SQL Server with T-SQL string concatenation.

Solution

Prior to SQL Server 2012 concatenation was accomplished by using the plus (+) sign. This was used to concatenate fields together of various data types (varchar, char, int, numeric, etc.). The limitation of this method is if any of the fields you are concatenating are NULL, the final string value is NULL. In SQL Server 2012 and later there is the 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.

SQL Concatenate Valeus with Plus Sign

For this demo I am going to use the Person.Person table from the AdventureWorks2012 database to demo the SQL functions to generate a full name for creating mailing labels. First, the following example is the old technique to concatenate strings using the + sign (concatenation operator):

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.

Using ISNULL to Corrects Nulls when Concatenating SQL Server Column Values

Below is example syntax is using ISNULL along with the plus sign to concatenate values. 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.

Concatenate SQL Server Column Values with CONCAT Function

The next set of code is using the new CONCAT() function that is in SQL Server 2012 and later versions with a SELECT statement. It replaces NULL values with an empty string of type VARCHAR(1). This SQL statement is much easier to read and write when you need to have NULL code handling in place and generate a single string in a single column with spaces as a separator.

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.

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

• Check out these additional resources for this SQL tutorial:
  • New FORMAT and CONCAT Functions in SQL Server 2012
  • Deciding between COALESCE and ISNULL in SQL Server
  • CONCAT()
  • String Concatenation +
  • ISNULL
• Learn about these additional T-SQL string functions:
  • SQL String Functions Overview
  • New FORMAT and CONCAT Functions in SQL Server 2012
  • CONCAT and CONCAT_WS function in SQL Server
  • SQL Server SUBSTRING
  • Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions

Chad Churchwell

Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication. He has been in IT for 14 years, working with SQL Server for 10 years, and is currently a enior DBA Consultant with Pragmatic Works. He is active in the community speaking at several SQL Saturday events.