GROUP_CONCAT comma separator - MySQL

Question

I have a query where I am using GROUP_CONCAT and a custom separator as my results may contain commas: '----'

This all works well, however it is still comma separated, so my output is:

Result A----,Result B----,Result C----

How can I make it so the output is:

Result A----Result B----Result C----

I thought this was the idea of a custom separator!

Failing that, can you escape commas in your results, so I can explode in PHP by the GROUP_CONCAT commas?

Where are the commas coming from? Are they the separators, or part of the results? That part of the question isn't clear to me. — Matt Fenwick, Oct 07 '11 at 19:14
GROUP_CONCAT(artists.artistname, '----') is my group concat line - as you can see, I have not put comma as the separator - they are not in the results but show in the output — user984580, Oct 07 '11 at 19:15

score 384 · Answer 1 · answered Oct 07 '11 at 19:17

384

Looks like you're missing the SEPARATOR keyword in the GROUP_CONCAT function.

GROUP_CONCAT(artists.artistname SEPARATOR '----')

The way you've written it, you're concatenating artists.artistname with the '----' string using the default comma separator.

answered Oct 07 '11 at 19:17

Joe Stefanelli

score 27 · Answer 2 · answered Apr 27 '18 at 09:46

27

Query to achieve your requirment

SELECT id,GROUP_CONCAT(text SEPARATOR ' ') AS text FROM table_name group by id;

answered Apr 27 '18 at 09:46

Vallabh Bothre

score 1 · Answer 3 · answered Jan 14 '16 at 15:27

1

Or, if you are doing a split - join:

GROUP_CONCAT(split(thing, " "), '----') AS thing_name,

You may want to inclue WITHIN RECORD, like this:

GROUP_CONCAT(split(thing, " "), '----') WITHIN RECORD AS thing_name,

answered Jan 14 '16 at 15:27

Roman

3 Answers3