MYSQL search in comma deliminated field.
Posted on 2008.11.26 by Tony G.
It seems it is not possible to combine the MySQL IN command with a field name, even when that field contains comma delimited values. MySQL may match the first value, but not any of those following the comma.
SELECT name FROM pet_owners WHERE 'FIDO' IN (pet_owners.pets)
The above code doesn’t work, I suspect because
... IN (pet_owners.pets) ...
is treated not as
... IN ('FLUFFY' , 'FIDO' , 'SPOT') ...
but as
... IN ('FLUFFY,FIDO,SPOT') ...
THE SOLUTION
One method is to use Regular Expresisons:
SELECT name FROM pet_owners WHERE pet_owners.pets REGEXP '[[:<:]]FIDO[[:>:]]'
Another way would be to use the FIND IN SET command:
SELECT name FROM pet_owners WHERE FIND IN SET ('FIDO', pet_owners.pets) > 0
The pet_owners.pets field does not have to be set to datatype SET as long as it has comman separated values.
