group_concat text field in mysql

Question

Tried to group_concat a text field in mysql.But its taking only the first value.(if the values are '177,178') it taking only 177 because I am saving this as a text field.So how can I do group_concat with this fields?

My query looks as such:

SELECT GROUP_CONCAT(abc.displayValue SEPARATOR ' ') FROM abc WHERE abc.lookupId IN ('177,178')

Kulasangar · Answer 1 · 2016-12-08T12:19:14.807

0

Are you misplacing the quotes within your IN?

SELECT GROUP_CONCAT(abc.displayValue SEPARATOR ',') FROM abc WHERE abc.lookupId IN (177,178)

edited Dec 08 '16 at 12:19

answered Dec 08 '16 at 12:16

Kulasangar

9,046
5
51
82

Why would a missing comma cause this problem? – Barmar Dec 08 '16 at 12:17
Comma is the default separator, so I think he really wants to use space instead. – Barmar Dec 08 '16 at 12:17
@Barmar yes you're right! It's not a must to have it though. +1 for pointing it out! I've edited the answer. – Kulasangar Dec 08 '16 at 12:18

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

The problem isn't with GROUP_CONCAT, the problem is that your WHERE clause is only selecting one row. Since the lookupId field is an integer, it's converting the string '177,178' to an integer, which is just 177. You shouldn't have quotes around the values in the IN() list, that's making it just a single value to look for.

SELECT GROUP_CONCAT(abc.displayValue SEPARATOR ' ') 
FROM abc 
WHERE abc.lookupId IN (177, 178)

If the comma-separated string is actually coming from a column in a table you're joining with, see sql join tables where 1 column has comma.

SELECT GROUP_CONCAT(table1.displayValue SEPARATOR ' ')
FROM table1
JOIN table2 ON FIND_IN_SET(table1.lookupID, table2.lookupIDs)
WHERE ...

edited May 23 '17 at 12:24

Community

1
1

answered Dec 08 '16 at 12:16

Barmar

741,623
53
500
612

Actually I am saving this lookupID as a varchar field since I have to store both 177,178 in a single row(may be I need to store more values in a single row such as 179,180 in future).So the table storing the values in the format '177,178'(like 'abc').Its taking this 177,178 as a single charcter.So how to group concatinate this? – rekha Dec 09 '16 at 04:08
You shouldn't store comma-separated values in a single field, you should use another table where each `lookupID` is in a different row. But if you're stuck with this, see http://stackoverflow.com/questions/25611999/sql-join-tables-where-1-column-has-comma/25612053#25612053. – Barmar Dec 09 '16 at 16:36

group_concat text field in mysql

2 Answers2