Search code examples
sqlstring-concatenation

SQL - Is it possible to exclude columns from concatenation if NULL?

I have addresses in 5 columns:

AddressLine1 AddressLine2 AddressLine3 AddressLine4 Country

I would like to concatenate these into one column with a comma separating each address line. If the addressline is NULL, then I'd like to not include it, as using a function like IFNULL(AddressLine1,'') will result in 2 commas, one after the other which is what I want to avoid.

Is it possible to exclude NULL to avoid this? Obviously depending on the address either all columns can be populated or only some e.g AddressLine1,AddressLine3,County for each with AddressLine2 and AddressLine4 being NULL

Using ISNULL to return an entry string will result in consecutive commas

Solution

  • NULL values are invisible in the concat result. So do something like this.

    select concat(address1+', ', address2+', ', address3+', ', country) as Address
from addresses

    If you also have empty string values then you should convert them into NULLs. Something like this.

    select concat(nullif(address1,'')+', ', nullif(address2,'')+', '....

    It works fine on MS SQL Server starting 2008 or earlier.