SELECT AS & NULL Values

Transact-SQL
1

Afternoon all,

Just a quick one I came accross this morning, If I use something like:

SELECT (Road + ', ' + Area + ', ' + City + ', ' County + ', ' Postcode) As Address 
FROM Employees
WHERE ID = 1000

If all fields have data in them then it returns fine, however if any field has a 'NULL' value then the only thing returned is 'NULL'....

I'm a little confused as to why? Also is there a work around for it?

Thanks

Dave

EDIT: I'm using SQL Server 2008R2

2

Please use the below query:

SELECT (isnull(Road,'') + ', ' + isnull(Area,'') + ', ' + isnull(City,'') + ', ' + isnull(County,'') + ', '+ isnull(Postcode,'')) As Address 
FROM Employees
WHERE ID = 1000

NULL values are not compatible with any other values. Because of the reason any values in the expression has NULL it is nullifying the result.

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

3

Ahhhh yes, I feel I should have known that.... Great explanation, thank you!

4

I would do this instead:

isnull(Road + ', ', '') +

you still wind up with a trailing comma - no need to add one to the last element that is concatenated, but if that can be NULL then you will get the comma that is appended to the penultimate element and so on ... Maybe a trailing comma is not a nuisance for you though ... I find it annoying to have to program around as I think of it as wasted CPU cycles!