MySQL - selecting address fields

Dr Pepper

Hi,

I have a table called site, with four fields called address1, address2, address3, address4 (among others). I want to concatenate the address fields in a one-line, comma separated string, for example:

select concat(address1,', ', address2,', ',address3,', ',address4) from site;
Example Output: 1 Main St, Blackrock, Dundalk, Co Louth

Which is fine.. however, when some of the address fields are balnk the output looks like:
10 Dawson St, Dublin 2, ,
Even worse, if they're all blank, the output looks like: , , ,

So what I want to do is only select a comma after an address field if the address field is not blank..

Anybody know how to do this? Thanks

smack_monkey

You could use CONCAT_WS(), but you'll have to set your blanks to NULL values. From the MYSQL reference manual:

---

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.


mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');

-> 'First name,Second name,Last Name'

mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');

-> 'First name,Last Name'

---

So set up your address fields so that blanks default to NULL. Use

SELECT CONCAT_WS(',',address1,address2,address3,address4) FROM site;

Which (hopefully) should work.

Dr Pepper

That's cool thanks smack_monkey. It would be too much of a hassle to make all the blank strings null in the project I'm currently working on.. But I have used concat_ws with trim (also listed on that page) to give the desired result.

select trim(both ', ' from concat_ws(', ',address1,address2,address3,address4))

It takes all of the ', ' strings off the beginning and end of the result...

Thanks again!