Search code examples
mysqlstring-concatenationis-empty

Hide seperator when response is empty

I am trying to select the address, zipcode, city and country of my customers from the database. I want to show these columns in one column. So I am using CONCAT_WS to select the address details of my customers:

SELECT CONCAT_WS(', ', address, zip, city, country) AS address2 FROM customers

When there is data in my table I get the correct response:

Street, 1234, City, Belgium

When there is no data, The statement shows only the comma`s:

, , , Belgium

I want to hide these commas if there is no data. Does someone know how I can hide these commas?

Solution

  • CONCAT_WS() ignores NULLs, but it does not ignore empty strings like ''. These are not the same thing in MySQL.

    To work around this, you can use the NULLIF() function. This returns NULL if its two arguments are equal.

    SELECT CONCAT_WS(', ', 
    NULLIF(address, ''), 
    NULLIF(zip, ''), 
    NULLIF(city, ''), 
    NULLIF(country, '')
  ) AS address2 FROM customers