0

Is there a way to ignore whitespace using group_concat, the same way that concat_ws does? group_concat_ws does not seem to be a thing even though this answer suggests using it.

I'm using something like this:

select phone.id,
group_concat(ac.color separator ', ') as 'Available Colors'
from phone_types phone
inner join available_colors ac on phone.id = ac.id
group by phone.id;

And I'm getting results like this:

id    Available Colors
1     , 
2     red, blue
3     , , green
4
5     ,
6     red
7     ,blue,
8     green,

I'm trying to get results like this:

id    Available Colors
1      
2     red, blue
3     green
4
5     
6     red
7     blue
8     green
StarSweeper
  • 397
  • 2
  • 4
  • 28

1 Answers1

2

If the value is NULL, group_concat will ignore the field. The reason you see blank is because they are empty strings. You can do this:

group_concat( if(ac.color='',null,ac.color) ) as ...
Schien
  • 3,855
  • 1
  • 16
  • 29
  • Thank you :) That removes the extra commas nicely, but leaves a bunch of ugly null symbols. ```ifnull(group_concat(if(ac.color='',null,ac.color) separator ', '),'') as ...``` worked perfectly though. – StarSweeper Oct 29 '19 at 20:14