Use a comma-separated string in an `IN ()` in MySQL

Question

If a user-defined variable @x is a string of comma separated numbers, e.g. '1,2,4', is there a way to use it in an IN () function?

Specifically:

SET @x := '1,2,4';
SELECT * FROM t WHERE c IN (@x);

does not select rows in t where c equals 1 or 2 or 4.

Check this :: http://stackoverflow.com/questions/10480568/comma-separated-values-in-mysql-in-clause — Sashi Kant, Dec 03 '12 at 09:41

score 3 · Accepted Answer · answered Nov 22 '10 at 14:59

3

You can't do this directly using a variable. Based on what you have now, the server is trying to execute select * form t where c in ('1,2,3'); ... which clearly isn't what you want given that you're checking to see if c contains the single string given.

You must generate dynamic SQL based off of your list in order to get this to work.

answered Nov 22 '10 at 14:59

Donnie

45,732
10
64
86

Fair enough. I'll put the values in a wee temp table and join. – Nov 22 '10 at 18:47

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

Maybe there is no point of answering this question now, but recently I faced same problem and this is how I resolved it.

It worked for me, hope this is what you were looking for.

select * from table_name where CONCAT(',',('1,2,4'),',') LIKE CONCAT('%,',column_name,',%');

Example: It will look like this

select * from t where ',1,2,4,' LIKE '%,2,%';

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 04 '20 at 13:21

Vi8L

958
10
12

score 1 · Answer 3 · answered Jul 29 '21 at 11:59

1

Try this:

SET @x := '1,2,4';
SET @sql = CONCAT('SELECT * FROM t WHERE c IN (', @x, ');');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Jul 29 '21 at 11:59

iZeroGue

11
4

Use a comma-separated string in an `IN ()` in MySQL

3 Answers3

Linked