MySql concat a character except for the last record

Question

How can I concat a character to MySQL results except for the last result?

So:

friends
-------------
friend-a
friend-b
friend-c
friend-d
friend-e

I now do select concat(friends,',') from friends which actually gives me each friend name with a ,, but I don't need the , for the last one.

I cannot use group_concat here due to it size restriction.

Attempting output

friend-a,
friend-b,
friend-c,
friend-d,
friend-e

Change the size restriction, run this before your query `SET SESSION group_concat_max_len = 1000000;` — Mihai, Oct 06 '14 at 16:40
@Mihai And if its more than 1000000. What is the maximum value for group_concat_max_len? Does having it set too high affect performance in any way? — Norman, Oct 06 '14 at 16:42
With session keyword the change is limited to session,restart mysql it will revert back to normal size.Max is 18446744073709551615 bytes,so in computer science terms is a big ass number.It will put things into memory so if you have a godzillion values you might get a performance hit. — Mihai, Oct 06 '14 at 16:45
You might also want to take a look here http://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size — Mihai, Oct 06 '14 at 16:55

score 2 · Accepted Answer · edited May 23 '17 at 12:05

if you dont want to use group concat (which would be the optimal way to do this.. you can remove the last row then add back on with a union or use an aggregate function on your string to return the largest.

(select concat(friends,',') as friend
 from friends 
 WHERE friends <> (SELECT friends 
                   from friends 
                   order by friends DESC 
                   limit 1)
)
UNION 
(SELECT friends 
 from friends 
 order by friends DESC 
 limit 1)

Fiddle Demo

An even better way to do it is like so

SET @a := (SELECT MAX(friends) FROM friends);
SELECT 
    CASE 
      WHEN friends <> @a 
      THEN concat(friends,',') 
      ELSE friends 
    END AS friends
FROM friends
GROUP BY friends

Another Fiddle

However the easiest way is to just use group_concat and increase the size for your session... not in your .cnf file but just for the session.. that way you don't affect the server as much for storage for that.

See My Answer Here for how to set your session max len and use it

score 0 · Answer 2 · answered Oct 06 '14 at 16:49

0

I guess that you want the output so that you can use it in the IN parameter of sql? If yes, then you can use the following :-

select * from `some_other_table` where `friends` in (select `friends` from `friends`);

Tell me if I am wrong

answered Oct 06 '14 at 16:49

Deval Khandelwal

3,458
1
27
38

score 0 · Answer 3 · answered Oct 06 '14 at 16:50

0

I was think that if the final friends value has the highest ID you could try something like this:

 SELECT 
   CASE friends 
      WHEN (ID != (SELECT MAX(ID) FROM TABLE)) THEN friends 
      ELSE concat(friends,',') END
 FROM
   FRIEND_TABLE

Cheers!

answered Oct 06 '14 at 16:50

Aziz

1,584
4
23
43

MySql concat a character except for the last record

3 Answers3