Mysql Variable With Multiple Values

Question

The following works as expected when there is a single value stored in a variable:

Set @var = 121;
select * from table where id = @var;

How can I set variable with multiple value and then use it in a query. I have tried this but it doesn't work:

set @var = (
  117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,
  117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,
  117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191
)
select * from table where id = @var;

xlecoustillier · Accepted Answer · 2015-10-15 10:10:08Z

5

set @var = '
  117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,
  117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,
  117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191'

SELECT * FROM table WHERE FIND_IN_SET(id,@var);

Thanks buddy

edited Oct 15, 2015 at 10:10

xlecoustillier

16.4k15 gold badges64 silver badges88 bronze badges

answered Oct 15, 2015 at 9:48

jai dutt

7906 silver badges13 bronze badges

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

Comments

Hitesh Mundra · Accepted Answer · 2015-10-15 10:14:34Z

You can to this by set the @var as comma separated string and use like to get the data. This is not perfect but workable and you have to take care about the value in @var. There should n't be any invalid char like space

Mysql> set @var = '117,120,121,122,143,151,175,233,387,189,118,119,339,357,500,501,493,425,307,191,117,120,121,122,143,151,175,233,12';
Query OK, 0 rows affected (0.00 sec)

Hitesh> select * from test where ((@var like concat(id,',%')) or  (@var like concat('%,',id)) or (@var like concat('%,',id,',%')) or  (@var like id));
+----+------+-------+
| ID | NAME | VALUE |
+----+------+-------+
| 12 | Nee  |  NULL |
+----+------+-------+
1 row in set (0.00 sec)

You can also use LOCATE function instead of like. MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start. If this position is not mentioned, searching starts from the beginning.

syntax LOCATE(substr,str)

mysql> SELECT LOCATE('st','myteststring'); 
+-----------------------------+
| LOCATE('st','myteststring') |
+-----------------------------+
|                           5 | 
+-----------------------------+

FIND_IN_SET() is perfect one for your case. It must have string values as comma separated.

Eyescream · Accepted Answer · 2015-10-15 09:32:08Z

-1

You should use IN

select * from table where id IN ( @var );

answered Oct 15, 2015 at 9:32

Eyescream

9215 silver badges14 bronze badges

1 Comment

xlecoustillier Over a year ago

This raises an "ERROR 1241 (21000): Operand should contain 1 column(s)".

Collectives™ on Stack Overflow

Mysql Variable With Multiple Values

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related