MySQL concatenating fields but ignoring empty ones

Question

I have a MySQL db with a list of people, including their address, which I want to return as one field in a query. It's split into address1, address2, address3, address4, post_code and I want to do something like the following

SELECT CONCAT(`address1`, ' ',  `address2`, ' ', `address3`, ' ', `address4`, ' ', `post_code`) AS `address` FROM `table`

So I'll end up with a full string of their address in address which works fine but if some of the fields are empty then I'll end up with a lot of double spaces. How can I eliminate the extra spaces? Is there an easier way than doing an IF() on each field?

Answer 1

use CONCAT_WS instead CONCAT

SELECT CONCAT_WS(' ',`address1`, `address2`,  `address3`,  `address4`,  `post_code`) AS `address` 
FROM `table`;

Answer 2

SELECT  CONCAT_WS(' ',  NULLIF(address1, ''),  NULLIF(address2, ''),  NULLIF(address3, ''),  NULLIF(address4, ''),  NULLIF(post_code, ''))
FROM    table

If your empty fields are NULL in fact, you can omit the NULLIF constructs:

SELECT  CONCAT_WS(' ',  address1, address2, address3, address4, post_code)
FROM    table

Answer 3

Instead of IFNULL, you could also use COALESCE, it's made for this task.