mysqlsqlmysql-5.7find-in-set

Find comma separated value in a column by using comma separated list

I have a a column item_id_list with comma separated id such as this:

enter image description here

I can search them by using FIND_IN_SET() such as this:

SELECT * FROM task_detail WHERE FIND_IN_SET('21', item_id_list)

but I need to check whether multiple id did exist in the row but adding another id to search will not work as stated in the docs.

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set

This function does not work properly if the first argument contains a comma (,) character.

SELECT * FROM task_detail WHERE FIND_IN_SET('21, 79', item_id_list)

Without changing my table structure, how do I solve this problem? Right now looping FIND_IN_SET() by number of id is the solution.

Solution

  • You could use a OR with find_in_set 

        SELECT * FROM task_detail 
    WHERE FIND_IN_SET('21', item_id_list)
    OR  FIND_IN_SET('79', item_id_list)