- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- How to add form validation for empty input fields with JavaScript?
- New line separator doesn't work for group_concat function in MySQL? How to use it correctly?
- Using GROUP_CONCAT() on bit fields returns garbage in MySQL? How to fix?
- How to add separator to numbers using MySQL views?
- How to add a separator for a choice box in JavaFX?
- How to add a separator to a Menu in JavaFX?
- How to add separator in a ToolBar with Java?
- How to quote values using MySQL group_concat?
- Implement numbering in MySQL GROUP_CONCAT
- Substring() for Fields in MySQL?
- How to add a separator in Menu item in Tkinter?
- Removing empty fields from MongoDB
- How to use GROUP_CONCAT in CONCAT in MySQL?
- Add DATE and TIME fields to get DATETIME field in MySQL?
- How to add a specific character to any empty space in MySQL table values?

Advertisements