pedalpete pedalpete - 10 months ago 76
MySQL Question

mysql concat with trim

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 there a better way of building my concat statement?

Answer Source

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;