Today a customer of mine was trying to query a user's full name out as one column, rather than as three separate columns, FirstName, MiddleName and LastName. He was having problems with the users who did not have a MiddleName, because the NULLs were causing his concatention to come back empty. This happens because by default, any string concatenation (+) that includes a NULL value will also produce a NULL result. This post will show you two ways to work around that, using the AdventureWorks2012.Person.Person table.
Option #1 - CASE
First I pull out the three columns separately, followed with my customer's statement and finishing with my CASE on the MiddleName. The end result for records with no MiddleName is derived from First and Last name, rather than NULL:
SELECT
FirstName,MiddleName,LastName,
FirstName
+ ' ' + MiddleName +' '+ LastName [NoGood],
CASE
WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName
WHEN MiddleName IS NOT NULL THEN FirstName + ' ' + MiddleName + ' ' +
LastName END [FullName]
FROM
Person.Person;
Output:
Option #2 - CONCAT_NULL_YIELDS_NULL
Even easier, you can use CONCAT_NULL_YIELDS_NULL to tell the server to treat the NULLs like empty strings instead of NULLs. Run exactly the same query as above, but prefaced like this:
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT
FirstName,MiddleName,LastName,
........
....
Output:
Pretty snazzy. BUT -- they say the CONCAT_NULL_YIELDS_NULL will always be ON in a future release, so this 2nd option is not a long-term solution. See here for more details: