Geek Answers Handbook

Mysql concat with trim

I have a table that contains a bunch of addresses in cells marked with the address | town. I am trying to combine the full address into a common format "address, city".

Sometimes, in my database, I will have one of the location cells empty. So I do IFNULL in my concat line, but in the end I have a leading or trailing ",". I tried the "trim" function along with my concat, but sometimes I get hooked with ",".

This is how I wrote my request

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

Any idea why I would have this behavior? Is there a better way to create my concat statement?

+4
source share
3 answers

I think your request is just missing the decimal space in the BOTH statement. It seems to work for me.

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

It's been quite a while, 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 the following:

 High Street, Southampton London Station Road London Road, Brighton
+1
source

disabling consecutive delimiters like "a, b ,, c, d, e ,,, eed, sffre" seems elusive for any elegant GROUP_CONCAT solution

The methods used are to create representations for each condition that avoids consecutive delimiters, and then combine or combine individual such representations.

-1
source

More articles:


All Articles