How to pass comma separated value in mysql query?

Question

I am using a sub query to get all qcodes and passing it as a parameter to another query in mysql. But inner query is returning value like -

SELECT qcodes FROM boardmst WHERE id=10

10002','10028','10031','10202','10226

so how to parse it to pass in another query with IN clause?

SELECT * FROM users WHERE qcodes IN (SELECT qcodes FROM boardmst WHERE id=10)

What do you mean with "But inner query is returning value like -" — dns_nx, Nov 21 '17 at 12:04
It is returning value with single quote 10002','10028','10031','10202','10226. but single quote is unavailable in first and last position. It is considering whole as a string like "10002','10028','10031','10202','10226" — Yugal Kishor Bais, Nov 21 '17 at 12:06
What is the datatype of `qcodes`? Also add the insert query for `boardmst` table. — Harshil Doshi, Nov 21 '17 at 12:07
what about find_in_set() function? https://stackoverflow.com/questions/41061181/mysql-passing-data-to-in — Christian Felix, Nov 21 '17 at 12:08
what is the result of `SELECT * FROM users WHERE qcodes IN (SELECT qcodes FROM boardmst WHERE id=10);`? — Harshil Doshi, Nov 21 '17 at 12:40
both have varchar datatype, problem it that it is taking it as a single string — Yugal Kishor Bais, Nov 21 '17 at 13:15
This query fetches nothing "SELECT * FROM users WHERE qcodes IN (SELECT qcodes FROM boardmst WHERE id=10)" . but when i am passing parameter manually like - "SELECT * FROM users WHERE qcodes IN ('10002','10028','10031','10202','10226')" It fetches some results... — Yugal Kishor Bais, Nov 21 '17 at 13:36
Are you running this query with some PDO or PreparedStatement in any language? Or you running directly on database? — Harshil Doshi, Nov 21 '17 at 14:25

score 0 · Answer 1 · answered Nov 21 '17 at 12:05

0

Apart from the missing quotes on the outside (maybe it's not the exact answer the inner question produces) this is exactly what MySQL expects as the content of an IN clause. You just have to be sure that the definition of the qcodes column is identical between the users and the boardmst tables.

answered Nov 21 '17 at 12:05

Niels Hietbrink

16
1
2

both have varchar datatype – Yugal Kishor Bais Nov 21 '17 at 13:15

score 0 · Answer 2 · answered Nov 21 '17 at 14:48

0

If you have only five values as comma separated then below query can come in handy :

SELECT * FROM users WHERE qcodes IN (
select regexp_substr(replace(qcodes,''',''',','),'[^, ]+',1,level)
from boardmst where id = 10 connect by level <= 5)

answered Nov 21 '17 at 14:48

Nikhil Shetkar

346
1
9

How to pass comma separated value in mysql query?

2 Answers2