Different delimiter in group concat

Question

I have an sql query like this:-

 REPLACE(
  GROUP_CONCAT( 
   IF( 
    (timediff(delta_ts,creation_ts) > '03:00:00')    
     && (priority='P5') ,bug_id,'')
     ),',,','' )
   AS exceeded_bugs
  from bugs
  ......

The result I got:-

exceeded_bugs: ,3743331,3743332,3743333

I need different delimiter since, the default delimiter of Group concat is ",". I need to separate the bugs using space or "|" or "-" symbol.

I tried giving :-

 REPLACE(
  GROUP_CONCAT( 
   IF( 
    (timediff(delta_ts,creation_ts) > '05:00:00')    
    && (priority='P6') ,bug_id,'')
    ) 
    ,SEPARATOR '-' ) 
   AS exceeded_bugs 
  from bugs
  .....

I got error:-

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR '-' ) as exceeded_bugs at line 1

Please help to correct the sql syntax of group concat with a different separator.

Don't think the comma before SEPARATOR is required. – Kickstart Jun 27 '13 at 10:27 — Kickstart, Jun 27 '13 at 10:27

echo_Me · Accepted Answer · 2013-06-27T11:35:59.910

3

dont use the comma before SEPARATOR

first you are not including separator inside group_concat function .

second you are not making anything with replace function

Take a look here

EDIT:

   REPLACE(GROUP_CONCAT( IF( (timediff(delta_ts,creation_ts) > '03:00:00') && (priority='P5') ,bug_id,'') SEPARATOR '-'),',,','' ) as exceeded_bugs

edited Jun 27 '13 at 11:35

answered Jun 27 '13 at 10:36

echo_Me

37,078
5
58
78

1

any other kind of group concat method? – Monisha Jun 27 '13 at 10:45
The SEPARATOR in that code fragment is within the REPLACE rather than the GROUP_CONCAT (not that any replace is being done) – Kickstart Jun 27 '13 at 10:46
i dont know why you using REPLACE yasmin and you are not replacing anything – echo_Me Jun 27 '13 at 10:49
1

I am using replace function , since the result is showing extra ',,' commas instead of bug ids. – Monisha Jun 27 '13 at 10:56
1

it is showing the result:- exceeded_bugs: --3743334--------------------------------- – Monisha Jun 27 '13 at 10:59
exceeded_bugs: ,,,3743331,3743332,3743333,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, – Monisha Jun 27 '13 at 11:03
how you want the result will be? – echo_Me Jun 27 '13 at 11:04
exceeded_bugs: 3743331-3743332-3743333 – Monisha Jun 27 '13 at 11:06
1

select sum( IF( (timediff(delta_ts,creation_ts) > '03:00:00') && (priority='P5') ,1,0)) P5_time_exceeded,REPLACE(GROUP_CONCAT( IF( (timediff(delta_ts,creation_ts) > '03:00:00') && (priority='P5') ,bug_id,'')),',,','' ) as exceeded_bugs from bugs where bugs.product_id=237 and bugs.resolution ='FIXED' and bugs.creation_ts >='2013-06-16 00:00:00' and bugs.creation_ts <= '2013-06-18 08:00:00' and bug_status='RESOLVED' \G; – Monisha Jun 27 '13 at 11:09
1

*************************** 1. row *************************** P5_time_exceeded: 3 exceeded_bugs: ,3743331,3743332,3743333 – Monisha Jun 27 '13 at 11:09
actually in this query you are not using separator as i told u – echo_Me Jun 27 '13 at 11:12
i have used and i got the result :- – Monisha Jun 27 '13 at 11:28
1

I have posted my answer. Thanks. Please check Samir – Monisha Jun 27 '13 at 11:42

score 0 · Answer 2 · answered Jun 27 '13 at 11:41

Samir's reply was giving more hyphens in the result. I have slightly modified the query, and got the exact result.

Samir's query:-

 select sum( 
    IF( (timediff(delta_ts,creation_ts) > '03:00:00') 
    && (priority='P5') ,1,0))   P5_time_exceeded,
    REPLACE(GROUP_CONCAT( IF( (timediff(delta_ts,creation_ts) > '03:00:00') 
    && (priority='P5') ,bug_id,'') SEPARATOR '-'),',,','' ) as exceeded_bugs 
    from bugs 
     ......

Result I got:-


P5_time_exceeded: 3                                                                                                                                  
exceeded_bugs: ---3743331-3743332-3743333--------------------------                                                                           
1 row in set (0.00 sec)

Modified query

 select sum( 
   IF( (timediff(delta_ts,creation_ts) > '03:00:00') 
   && (priority='P5') ,1,0)) P5_time_exceeded,
   REPLACE(GROUP_CONCAT( IF( (timediff(delta_ts,creation_ts) > '03:00:00')
   && (priority='P5') ,bug_id,'') SEPARATOR '-'),'---','' ) 
   as exceeded_bugs 
   from bugs 
   where .....

Got the exact result:-

P5_time_exceeded: 3
   exceeded_bugs: 3743331-3743332-3743333

I am replacing the least multiple of repeating sybmol ,,, replaced with a space '', so that, the repeating symbol wont come.

Thanks Samir.

i just give a guess why you getting much --- is because you have empty values and it concatenated with sperator - so thats why you got many ------------- , it means you have fields empty concat fields empty ..... so i think you should give and condition to dont select empty values — echo_Me, Jun 27 '13 at 11:56

score 0 · Answer 3 · answered Jun 27 '13 at 13:38

Not sure you need the REPLACE.

GROUP_CONCAT should ignore NULL fields, but you are putting in empty occurances when you want to ignore one. Instead of using '' try using NULL instead.

Like this:-

SELECT SUM( IF( (TIMEDIFF(delta_ts,creation_ts) > '03:00:00') && (priority='P5') ,1,0)) AS P5_time_exceeded,
GROUP_CONCAT( IF( (TIMEDIFF(delta_ts,creation_ts) > '03:00:00') && (priority='P5'), bug_id, NULL) SEPARATOR '-') AS exceeded_bugs 
FROM bugs 
WHERE .....

Different delimiter in group concat

3 Answers3