Friday, May 11, 2012

Concatenate stings with nulls

I was pulling a report in SQL Server, and getting strange results.
 SELECT FIRSTNAME  + ' ' +
                 MIDDLENAMEORINITIAL + ' ' +
                 LASTNAME as ContactName,
                 EMAILADDRESS
FROM aTable
The result set had ContactName as null whenever any of the three fields were null.  Any field concatenated with a null will be null.  The way to fix that is to wrap a ISNULL function around the field like this.

 SELECT  ISNULL(FIRSTNAME,'') + ' ' +
                 ISNULL(MIDDLENAMEORINITIAL,'') + ' ' +
                 ISNULL(LASTNAME,'') as ContactName,
                 EMAILADDRESSS
FROM aTable
This works with no problem!