Group_Concat in Concat not working with NULL values

Question

I have a table

CREATE TABLE IF NOT EXISTS `dept` (
  `did` int(11) NOT NULL,
  `dname` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `dept` (`did`, `dname`) VALUES
(1, 'Hi'),
(2, NULL),
(3, 'Hello');

Then I have a query

select group_concat(concat(did,"','",dname) separator '),(') as Result from dept

It is producing result as 1','Hi'),('3','Hello

Question: How can I get result from above query as 1','Hi'),('2','NULL'),('3','Hello

It is missing the rows which have NULL values but I need to fetch all

Link for SQL Fiddle Demo of question

UPDATE: If I have more than one or all columns allowing NULL, Is there some way to apply COALESCE once for all or have to apply individually at each Column?

I have another example of groupconcat in a concat stackoverflow.com/questions/13451605/… — Sami
– Sami, Commented Jan 17, 2016 at 21:02

Sami · Accepted Answer · 2012-09-29 15:43:00Z

9

try this, use COALESCE

.., COALESCE(dname, 'NULL'),..

making it NULL string visible. SQLFIDDLE DEMO

edited Sep 29, 2012 at 15:43

Sami

8,4599 gold badges72 silver badges104 bronze badges

answered Sep 29, 2012 at 14:45

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sami Over a year ago

Please guide if two or all columns can have NULL values then do I have to use COALESCE with each column or there is some method to do this once for all?

John Woo Over a year ago

unfortunately none, you need to individually add COALESCE on each column.

Michael Berkowski · Accepted Answer · 2012-09-29 15:08:10Z

2

From the MySQL aggregate function documentation:

Unless otherwise stated, group functions ignore NULL values.

Use COALESCE() to replace the nulls with a string, since they would be eliminated by the aggregate function. For example COALESCE(dbname, 'NULL') will return the string NULL if dbname IS NULL. Its purpose is to return the first non-null of the arguments you give it, and can therefore return a default value.

SELECT
  GROUP_CONCAT(CONCAT(did,"','", COALESCE(dname, 'NULL')) SEPARATOR "'),('") AS Result
FROM dept

edited Sep 29, 2012 at 15:08

answered Sep 29, 2012 at 14:45

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

2 Comments

Sami Over a year ago

Your solution is quite well explained and acceptable. Please Just replace separator '),(' to "'),('" as I have done it in question and sqlfiddle demo

Michael Berkowski Over a year ago

@Sami edited above. You can also escape single quotes as in '\'),(\''

jsist · Accepted Answer · 2012-09-29 14:55:21Z

0

Hope following query will serve your purpose

SELECT GROUP_CONCAT(
IF(dname IS NOT NULL, CONCAT(did,"','",dname), CONCAT(did,"','NULL")) 
SEPARATOR '),(') AS Result FROM dept

answered Sep 29, 2012 at 14:55

jsist

5,2534 gold badges30 silver badges43 bronze badges

Collectives™ on Stack Overflow

Group_Concat in Concat not working with NULL values

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related