Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL concatenating fields but ignoring empty ones

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?

like image 415
jackbot Avatar asked Feb 26 '10 16:02

jackbot


2 Answers

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
like image 93
Quassnoi Avatar answered Oct 11 '22 20:10

Quassnoi


use CONCAT_WS instead CONCAT

SELECT CONCAT_WS(' ',`address1`, `address2`,  `address3`,  `address4`,  `post_code`) AS `address` 
FROM `table`;
like image 44
a1ex07 Avatar answered Oct 11 '22 20:10

a1ex07