Search code examples
mysqlgroup-concat

GROUP_CONCAT with condition

In the following example, I use group-concat to concatenate car and new_color.

GROUP_CONCAT(DISTINCT CONCAT( car, ' - ' , new_color) ORDER BY car SEPARATOR '; ') AS car_color

RESULT:

Fiat -;
Ford - red;
BMW - blue;

How to do the '-' (separating the car from new_color) be added only if new_color have a value.

In my example, the desired result is:

Fiat ;
Ford - red;
BMW - blue;
Solution

  • Use concat_ws:

    GROUP_CONCAT(DISTINCT CONCAT_WS(' - ', car, case when trim(new_color) = '' then null else newcolor end) ORDER BY car SEPARATOR '; ') AS car_color

    Like @Matteo Tassinari said, NULLIF(TRIM(new_color), '') will be more simple in mysql.