MySQL group_concat to add a separator for empty fields?
For this, you can use replace() along with group_concat(). Here, for empty fields, we are displaying a comma as a separator. Let us first create a table −
mysql> create table DemoTable
(
Id int,
Number varchar(100)
);
Query OK, 0 rows affected (2.03 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values(10,'456');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values(11,'345');
Query OK, 1 row affected (0.28 sec)
mysql> insert into DemoTable values(10,'');
Query OK, 1 row affected (0.63 sec)
mysql> insert into DemoTable values(10,'278');
Query OK, 1 row affected (0.35 sec)
mysql> insert into DemoTable values(10,'');
Query OK, 1 row affected (0.37 sec)
mysql> insert into DemoTable values(10,'789');
Query OK, 1 row affected (0.47 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+------+--------+
| Id | Number |
+------+--------+
| 10 | 456 |
| 11 | 345 |
| 10 | |
| 10 | 278 |
| 10 | |
| 10 | 789 |
+------+--------+
6 rows in set (0.00 sec)
Following is the query to add a separator for empty fields −
mysql> select Id,replace(group_concat(Number),',,',',') from DemoTable group by Id;
This will produce the following output −
+------+----------------------------------------+
| Id | replace(group_concat(Number),',,',',') |
+------+----------------------------------------+
| 10 | 456,278,789 |
| 11 | 345 |
+------+----------------------------------------+
2 rows in set (0.00 sec)
Published on 26-Sep-2019 11:01:41
