In the world of electronics and even in case of databases, we know that a NULL is different from a 0 value or a blank string. A NULL stands for undefined value, however a 0 or a blank string is some definite value.

I was recently asked how to prevent string concatenation operations with NULL from resulting into NULL.

When I first heard about the question, my response was – “NULL and blank strings have separate meanings. Why would you want to treat these values as one and the same during a concatenation operation?” I was told that the script had to generate a single address field by concatenating Address Lines 1 & 2, City, State, Postal Code and related information. Because fields like the Address Line 2 may be NULL, then the default behaviour would be that the entire result will become NULL.

The team were looking for validating one of the options that they had come up with, which is what prompted this post.

Let’s take an example:

USE AdventureWorks2012;

GO

SELECT pp.FirstName,

       pp.LastName,

       pbea.AddressTypeID,

       pa.AddressLine1 AS AddressLine1,

       pa.AddressLine2 AS AddressLine2,

       pa.AddressLine1 + CHAR(10) +

       pa.AddressLine2 + CHAR(10) +

       psp.StateProvinceCode + ‘ – ‘ +

       psp.CountryRegionCode AS PersonAddress

FROM HumanResources.Employee AS hre

INNER JOIN Person.Person AS pp ON hre.BusinessEntityID = pp.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress AS pbea ON hre.BusinessEntityID = pbea.BusinessEntityID

INNER JOIN Person.Address AS pa ON pbea.AddressID = pa.AddressID

INNER JOIN Person.StateProvince AS psp ON pa.StateProvinceID = psp.StateProvinceID;

GO

The AddressLine2 column in the Person.Address table is NULL for a lot of the records in the AdventureWorks2012 sample database. Do note that AddressLine1 is not a NULL value. The screenshot below shows the default behaviour of SQL Server when we execute the query provided above.

Changing concatenation behaviour of NULL at the query level

To change the behaviour of string concatenation with respect to NULL, one can use the SET option – CONCAT_NULL_YIELDS_NULL to OFF. Here’s a quick modification to the script above that demonstrates the usage of this SET option.

USE AdventureWorks2012;

GO

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT pp.FirstName,

       pp.LastName,

       pbea.AddressTypeID,

       pa.AddressLine1 AS AddressLine1,

       pa.AddressLine2 AS AddressLine2,

       pa.AddressLine1 + CHAR(10) +

       pa.AddressLine2 + CHAR(10) +

       psp.StateProvinceCode + ‘ – ‘ +

       psp.CountryRegionCode AS PersonAddress

FROM HumanResources.Employee AS hre

INNER JOIN Person.Person AS pp ON hre.BusinessEntityID = pp.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress AS pbea ON hre.BusinessEntityID = pbea.BusinessEntityID

INNER JOIN Person.Address AS pa ON pbea.AddressID = pa.AddressID

INNER JOIN Person.StateProvince AS psp ON pa.StateProvinceID = psp.StateProvinceID;
–Safety Check – turn CONCAT_NULL_YEILDS_NULL back ON

SET CONCAT_NULL_YIELDS_NULL ON

GO

The result of the query execution is shown below.

Changing concatenation behaviour of NULL at the database level

If the entire database requires that concatenation with a NULL should yield NULL, then it would be a good idea to also set the option on the database to OFF using the ALTER DATABASE statement shown below:

ALTER DATABASE AdventureWorks2012 SET CONCAT_NULL_YIELDS_NULL OFF

GO

The Correct Way

Personally, I would not go with either of the two options provided above for two reasons:

1. At the end of the day, NULL is different from a 0 or blank string. Both have distinct meanings and must not be mixed with each other
   
2. The second reason is that if we look at Books-On-Line, it clearly tells us that:

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The method that I consider to be the correct way in addressing the NULL concatenation problem is to use the ISNULL or COALESCE function as shown in the query below.

USE AdventureWorks2012 ;

GO

SELECT pp.FirstName,

       pp.LastName,

       pbea.AddressTypeID,

       pa.AddressLine1 AS AddressLine1,

       pa.AddressLine2 AS AddressLine2,

       pa.AddressLine1 + CHAR(10) +

       ISNULL(pa.AddressLine2, ”) + CHAR(10) +

       psp.StateProvinceCode + ‘ – ‘ +

       psp.CountryRegionCode AS PersonAddress

FROM HumanResources.Employee AS hre

INNER JOIN Person.Person AS pp ON hre.BusinessEntityID = pp.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress AS pbea ON hre.BusinessEntityID = pbea.BusinessEntityID

INNER JOIN Person.Address AS pa ON pbea.AddressID = pa.AddressID

INNER JOIN Person.StateProvince AS psp ON pa.StateProvinceID = psp.StateProvinceID;

GO

Further Reading

• CONCAT_NULL_YIELDS_NULL – [Books On Line Link]

Until we meet next time,

Be courteous. Drive responsibly.