Remove quote from query inside : IN() MySql

Question

If i give hard coded value inside query it works, but not in case of sub query or column given.

Here is small example of issue i am facing :

Following both query is a type of sub query, like its part of another query, so don't think that where is table 'm' and something else, as it is working already.

So, my query like :

1)

SELECT GROUP_CONCAT(CONCAT(a_u.first_name,' ', a_u.last_name)) AS associated_admin_u 
  FROM users a_u 
 WHERE a_u.id IN(m.associated_admin) 
 GROUP 
    BY m.id

And m.associated_admin will return a quoted string like '1,10' so this will not work because of its a string.

2)

 SELECT GROUP_CONCAT(CONCAT(a_u.first_name,' ', a_u.last_name)) AS associated_admin_u 
   FROM users a_u 
  WHERE a_u.id IN(1,10) 
  GROUP 
     BY m.id

If i write hard code like 1,10 it works, because it is not a string

So first one is not works because that query is part of another query as a sub query.

And i am sure this question couldn't be duplicate as i am facing it like in this way so any help would be appreciate, thanks reader!

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 08 '16 at 10:32
This answer may help http://stackoverflow.com/questions/4650238/split-a-mysql-string-from-group-concat-into-an-array-like-expression-list?answertab=active#tab-top — Rastislav Struhár, Dec 08 '16 at 10:39

jeroen · Accepted Answer · 2016-12-08T10:57:35.817

4

Based on your comments, you need something like:

SELECT GROUP_CONCAT(CONCAT(a_u.first_name,' ', a_u.last_name)) AS associated_admin_u 
  FROM users a_u 
 WHERE FIND_IN_SET(a_u.id, TRIM(BOTH '\'' FROM m.associated_admin)) 
 GROUP 
    BY m.id

This will first trim the quotes from m.associated_admin and then use FIND_IN_SET instead of IN so that you can use a string with comma-separated values.

edited Dec 08 '16 at 10:57

answered Dec 08 '16 at 10:52

jeroen

91,079
21
114
132

1

Perfect jeroen! this is exactly what i want, thanks +1 – Jigar7521 Dec 08 '16 at 11:20

score 0 · Answer 2 · answered Dec 08 '16 at 10:32

0

You can just create a subquery in IN for example:

SELECT group_concat(CONCAT(a_u.first_name,' ', a_u.last_name)) AS associated_admin_u 
FROM users a_u WHERE a_u.id IN(
   SELECT id FROM mytable WHERE id IN(1,10)
) GROUP BY m.id

answered Dec 08 '16 at 10:32

Daan

12,099
6
34
51

Thanks Daan but as i said it will not work if there are quote available at IN('1,10') like from in your query. – Jigar7521 Dec 08 '16 at 10:39
@Jigar7521 In that case you should probably normalize your database. – jeroen Dec 08 '16 at 10:41
Yes but that is not my solution, i want some kind of logic to remove quote from '1,10' – Jigar7521 Dec 08 '16 at 10:44
@Jigar7521 What quote? I haven't added a single quote inside the IN. – Daan Dec 08 '16 at 10:45
Ah you actually have ``1,10`` in your column. Thanks for making that clear @jeroen – Daan Dec 08 '16 at 10:52
ya i want to convert '1,10' into '1', '10' or 1,10 – Jigar7521 Dec 08 '16 at 11:13

Remove quote from query inside : IN() MySql

2 Answers2