Composite SQL Field with null Value Returns Blank

The intent was to join the address fields as a single returned value from a database query select. But I found that if one of these fields was null, the whole returned result was empty.

I had an address, in the usual way, across a number of database fields: address; town ; postcode and county.

I wished o let the database query do the work, joining the fields, as opposed to the VB/C# code.

The SQL query to join these fields as a single composite field value.

Shown below is my first version of the composite field value, derived from the individual address parameters.

‘Street: ‘ + building.address + ‘, Town: ‘ + building.town + ‘, Postcode: ‘ + building.postcode + ” AS Location

My original expectation was that if one of these address fields was null then that part would be shown as blank.

For example with no town the returned entry might be:

Street: Broad Street, Town: , Postcode:  Rg1 1AA

I found that whilst I could readily create a string composed from the individual fields on occasion the result was empty, even though I knew at least one field to have a valid value.

Investigating I showed that if one of the fields was empty (null) then the whole combined field had a null value.

The building location was to show the address, as street, town and postcode.

‘Street: ‘ + building.address + ‘, Town: ‘ + building.town + ‘, Postcode: ‘ + building.postcode + ” AS Location

As can be seen its a simple string addition of the individual fields.

However, if there was no entry in one of the fields, ie. it was null, then the whole result returned as an empty field, as opposed to the single empty entry.

To correct the empty result error I added an isnull test for each field, taking either the database value or an alternative presentational value, In this instance a couple of dashes to indicate that there is no entry.

‘Street: ‘ + ISNULL(building.address,’–‘) + ‘Town: ‘ + ISNULL(building.town,’–‘) + ‘Postcode: ‘ + ISNULL(building.postcode,’–‘) + ” AS Location

Shown above is the previous example with the addition of the isnull.

For our earlier example this gives:

Street: Broad Street, Town: –, Postcode:  Rg1 1AA