Getting funky with GROUP_CONCAT

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?

 

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?

 

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