Mysql group_concat adds a separator for empty fields

Question

There is a sample schema in here: http://sqlfiddle.com/#!2/c0723a/2

query is select id,group_concat(val) from test group by id

result is

ID GROUP_CONCAT(VAL)
1 ,64,66,,203,214,204

I wanted to concat val field without a comma for empty records like this

ID GROUP_CONCAT(VAL)
1 64,66,203,214,204

Vignesh Kumar A · Accepted Answer · 2014-03-20T08:34:13.000

5

Simply use Replace

select id,replace(group_concat(val),',,',',') from test group by id

Or you can use IF

select id,group_concat(if(val ='',null, val)) from test group by id

Or You can use NULLIF

select id,group_concat(Nullif(val,'')) from test group by id

edited Mar 20 '14 at 08:34

answered Mar 20 '14 at 08:28

Vignesh Kumar A

@xdazz Thanks for your comment. I have added alternative way too – Vignesh Kumar A Mar 20 '14 at 08:32
NULLIF run faster than IF on my complex query. Thanks – zgormez Mar 20 '14 at 08:46

score 4 · Answer 2 · edited Dec 23 '15 at 17:12

4

SELECT id, GROUP_CONCAT(val) FROM test 
WHERE val is not null AND val <> '' 
GROUP BY id

edited Dec 23 '15 at 17:12

answered Mar 20 '14 at 08:33

StanislavL

this should be the correct answer. it's simple and more accurate – John Feb 01 '17 at 02:53
Yes, this answer is correct, if there is no any other field in the query. But, when i want to select the records even if the val field is null it is not correct – zgormez Feb 27 '17 at 13:43

score 1 · Answer 3 · answered Mar 20 '14 at 08:26

1

Add an IF check, GROUP_CONCAT will skip the NULL value.

select id, group_concat(IF(val = '', null, val)) from test group by id

answered Mar 20 '14 at 08:26

xdazz

3 Answers3