Sam Lucas Sam Lucas - 7 months ago 10
SQL Question

Use a varchar in MySQL IN() Statement

I have a database which holds different states, I am trying to filter on these states using the id which is a smallint.

To do this I am using the IN() statement:

Declare StateList varchar(150);
Declare States varchar(150);
set @StateList = State;
set @States = '';

if (@StateList Like '%1%')then
if(LENGTH(@States) > 0) then set @States := Concat(@States, ',1,2');
else set @States := Concat(@States, '1,2');
end if;
end if;
if(@StateList Like '%2%')then
if(LENGTH(@States) > 0) then set @States := Concat(@States, ',3');
else set @States := Concat(@States, '3');
end if;
end if;
if(@StateList Like '%3%')then
if(LENGTH(@States) > 0) then set @States := Concat(@States, ',4,5');
else set @States := Concat(@States, '4,5');
end if;
end if;
Select *
from ticket_state
Where ticket_state in (@States)


Now when the input is say
12
the value of
@States
should be
1,2,3
which I was hoping would go straight into the in statement as
in(1,2,3)
but this is only returning the first number in the sequence for states, which is what I don't want.

Any one know why this isn't working?

Answer

Statement like IN(1,2,3) would do do job, but that's not what you have here. You have a single string, so it's like IN('1,2,3')

You can use FIND_IN_SET function to achieve what you want.

example:

SELECT FIND_IN_SET(2, '1,2,3'); -- returns 2

Try:

SELECT * 
FROM ticket_state
WHERE FIND_IN_SET(ticket_state,@States)