SQL Server – Concat with NULL

Sorry I haven’t posted much lately. I got married a couple of Saturdays ago, then went on my honeymoon for a week. But I’m back now and better than ever.

***

I had a “duh” moment this morning as I was trying to figure out why my SQL code was returning NULL values. I had something to this effect:

SELECT firstName + ' ' + lastName FROM employees WHERE active = 1

As it turns out, if either firstName or lastName have are NULL, the concatenation will return NULL. I guess I just assumed it would treat NULL as an empty string in this case, but no such luck. Keep this in mind when storing NULL values in text fields that may need concatenation at some point.