Getting funky with GROUP_CONCAT

Discussion in 'MySQL' started by sarahk, Aug 16, 2015.

0
  1. #1
    GROUP_CONCAT is awesome but I want to make it a bit more user friendly.

    SELECT GROUP_CONCAT(firstname,' ',lastname ORDER BY firstname SEPARATOR ',')
    Code (markup):
    Instead of getting results like

    I'd like

    Now I can swap out that last comma in PHP but I'd like to do as little as possible in PHP (for this particular feature) and have all the work done in the database.

    Any ideas on how to make the last two rows have a different SEPARATOR?
     
    sarahk, Aug 16, 2015 IP
  2. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,334
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #2
    GROUP_CONCAT isn't really designed for formatting. You really would be better off doing it at the application level (PHP).

    Now if for some reason, you absolutely MUST do it in SQL only for some reasons (again, a bad idea imo), you could do it like so:

    mysql> SELECT INSERT('Jane, Terry, Walter', LENGTH('Jane, Terry, Walter') - INSTR(REVERSE('Jane, Terry, Walter'), ',') + 1, 1, ' and');
+--------------------------------------------------------------------------------------------------------------------------+
| INSERT('Jane, Terry, Walter', LENGTH('Jane, Terry, Walter') - INSTR(REVERSE('Jane, Terry, Walter'), ',') + 1, 1, ' and') |
+--------------------------------------------------------------------------------------------------------------------------+
| Jane, Terry and Walter                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
    Code (markup):
    Adapting that for your example above, it would be like this:

    SELECT INSERT(GROUP_CONCAT(firstname,' ',lastname ORDER BY firstname SEPARATOR ','), LENGTH(GROUP_CONCAT(firstname,' ',lastname ORDER BY firstname SEPARATOR ',')) - INSTR(REVERSE(GROUP_CONCAT(firstname,' ',lastname ORDER BY firstname SEPARATOR ',')), ',') + 1, 1, ' and');
    Code (SQL):
    That also doesn't take into account sanitizing the names... so hopefully it's not possible for someone to input a comma in their name (like ", Jr." or something).

    Did I mention you should be doing that at the application level? :)
     
    digitalpoint, Aug 25, 2015 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,807
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #3
    I think you might be right. I can always do a group_concat with pipes and then back in the app explode it and rebuild.

    Oh, and none of the Jr crap for us Kiwis :)
     
    sarahk, Aug 25, 2015 IP