pedalpete pedalpete - 1 month ago 20
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

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;