Problem: I need to produce mailing labels from my Microsoft SQL Server database, but when I use the + sign to concatenate the first, middle, and last names together, I get NULL for a lot of rows. This makes it impossible to produce the full names. What are some options to address this problem?

Solution: Prior to SQL Server 2012, concatenation was accomplished by using the plus (+) sign to concatenate fields together of various data types (varchar, char, int, numeric, etc.). However, the limitation of this method is that if any of the fields you are concatenating are NULL, the final string value is also NULL.

In SQL Server 2012 and later versions, there is the CONCAT() function that replaces NULL with an empty string. This function can be very beneficial in your code. Let’s take a look at an example using the Person.Person table from the AdventureWorks2012 database to generate a full name for creating mailing labels.

First, let’s see the old technique of concatenating strings using the + sign (concatenation operator):

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

As you can see, 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.

To address this issue, you can use the ISNULL() function along with the plus sign to concatenate values:

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

By using the ISNULL function, the NULL values are replaced with an empty string. This achieves the same result as using the CONCAT() function, but requires more code and reduces readability.

Alternatively, you can use the new CONCAT() function introduced in SQL Server 2012 and later versions:

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

With the CONCAT() function, all MailingName values are present, even if some of the columns are set to NULL. This function replaces NULL values with an empty string, making it very useful for coding around NULL values.

In conclusion, when concatenating data in SQL Server, it is important to handle NULL values properly. The CONCAT() function introduced in SQL Server 2012 provides a convenient way to replace NULL values with an empty string. This simplifies the code and improves readability, making it easier to generate mailing labels or concatenate strings in general.