#0310 – SQL Server – CONCAT_NULL_YIELDS_NULL property


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.


image


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.


image


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.

Advertisements

One thought on “#0310 – SQL Server – CONCAT_NULL_YIELDS_NULL property

  1. Pingback: #0385 – SQL Server – Query Development and NULL values | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s