Search code examples
mysqlsqlgroup-concat

Mysql group_concat adds a separator for empty fields

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

Solution

  • 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

    Fiddle Demo