How to convert comma separated numbers to integer mysql query GROUP_CONCAT

Question

I have a comma separated numbers like '46,95,44' which is the output obtained from GROUP_CONCAT

select GROUP_CONCAT (prodid) from tablename where condition;

I want to convert this to integer such that i can use this as the input to the "in" operation

select * from table2 where prodid in 
(
   select GROUP_CONCAT (prodid) from tablename where condition
);

The above query results only base on the first number in the comma separated list . How to convert the list in to integer

output :  46,95,44

juergen d · Accepted Answer · 2014-08-28 11:19:46Z

3

Remove the group_concat

select * from table2 
where prodid in 
(
  select prodid
  from tablename
  where condition
);

group_concat produces a single string with ids, but you need seperate values for the IN clause.

With group_concat your query compiles to

select * from table2 
where prodid in ('46,95,44');

but you need

select * from table2 
where prodid in ('46','95','44');

answered Aug 28, 2014 at 11:19

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2651906 Over a year ago

if the "select prodid from tablename where condition" returns multiple rows it will pop an error.

juergen d Over a year ago

Then the error comes from something else. IN has no problem with multiple records. What is the complete error message?

Kuldeep Over a year ago

@juergend your solution is perfect no doubt, but query taking too much of time

juergen d Over a year ago

@Kuldeep: Then you can ask a question on Stack Overflow about it including table definition, example data, the query you tried and indexes you have on your tables.

Collectives™ on Stack Overflow

How to convert comma separated numbers to integer mysql query GROUP_CONCAT

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related