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)

AmitDiwan

Published on 26-Sep-2019 07:31:41

Previous Page Print Page