SQL Server, Nulls, and SET CONCAT_NULL_YIELDS_NULL

When you try to concatenate string columns in SQL Server, if any of the columns have null as a value, by default the result of the broader concatenation will be null. You can get around this by entering the following:

SET CONCAT_NULL_YIELDS_NULL OFF

For example, consider the following query:

SELECT last_name + ', ' + first_name + ' ' + middle_name AS full_name
FROM person
WHERE person_id = 12345

If person 12345 doesn't have a middle name, and person 12345's middle_name is set to null in the database, then the entire concatenation will resolve to null, and full_name will result in an null / empty string.

However, if you do this:

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT last_name + ', ' + first_name + ' ' + middle_name AS full_name
FROM person
WHERE person_id = 12345

Then the result will be something like:

-----------------------
Doe, John
-----------------------

By the way, this solution works just fine in ColdFusion too. For example:

<cfquery name=""get_name" datasource="ds">
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT last_name + ', ' + first_name + ' ' + middle_name AS full_name
FROM person
WHERE person_id = 12345
</cfquery>

This is especially useful when using cfgrid. CFgrid won't allow you to compose columns that represent more than one database field. For instance, you would have to use one cfgridcolumn for first_name, and a second for last_name. You can't just have both in one column called "Full Name". Unless, that is, you use the above example to create the full_name column thru the database.