Search code examples
mysqlsqlcoalesce

Intelligent coalesce

I have a table with some basic address data:

table structure

If Street and Postcode/Town are filled, I want Street and Postcode/Town separated by comma.

select Concat(coalesce(Street,''),", ", coalesce(Postcode,'')," ",coalesce(Town,'')) from adresses

If street is not available, I would like to have just the postcode and town separated by a blank, if even the post code is missing I want just the name (if just postcode is missing, I'd like to have "Street, Town")

How can I design the query so that it considers what is available and where comma and blank need to be put?

Solution

  • The easiest way to do this with with the built-in concat_ws():

    select concat_ws(', ', Street, Postcode, Town)
from adresses;

    If any of the arguments are NULL (except the separator), then concat_ws(), simply skips that string and the associated separator.