mysql concat with trim

Question

I've got a table which holds a bunch of addresses in cells labelled address | city. I am attempting to merge the complete address into the common 'address, city' format.

Occasionally, in my database, I will have one of the location cells empty. Therefore, I do a IFNULL in my concat line, but I end up with a leading or trailing ','. I have tried the 'trim' function along with my concat, but still get trailing ',' on occasion.

This is how I've written my query

SELECT TRIM(BOTH ',' FROM CONCAT(IFNULL(address,''), ', ', IFNULL(city,''))) FROM locals

any idea why I would have this behavior? is their a better way of building my concat statement?

Answer 1

This is quite long but it seems to work.

SELECT TRIM(CONCAT(IFNULL(address,''), IF(address IS NOT NULL AND city IS NOT NULL, ', ',''), IFNULL(city,''))) FROM locals

So with this data:

address          city
----------------------------
High Street   Southampton
NULL          London
Station Road    NULL
London Road   Brighton

You get this:

High Street, Southampton
London
Station Road
London Road, Brighton

Answer 2

I think your query is just missing a space after the comma in the BOTH statement. That seems to work for me

SELECT TRIM(BOTH ', ' FROM CONCAT(IFNULL(address,''), ', ', IFNULL(city,''))) FROM locals;