[SOLVED] MySQL, returning row using find_in_set is not working as expected

MySQL, returning row using find_in_set is not working as expected

I'm trying to run the following MySQL command:

SELECT ID, intervention_post_title, linked_article FROM wp_cpt_combined WHERE FIND_IN_SET (72, habitat_type)

Inside my database the column habitat_type (as this is a multiselect field) stores the values as"

id  habitat_type
1   [72, 74]
2   [70]
3   [71]

The above sql command returns no rows - what am I missing? I need to be able to pull back row 1 using find_in_set (72, habitat_type) or find_in_set (74, habitat_type) etc. Do I have to do a replace to remove the square brackets?

Any help would be greatly appreciated.

Solution

You can use REPLACE() function to replace occurences of [ and ] with empty string.

Try the following:

SELECT 
  ID, 
  intervention_post_title, 
  linked_article 
FROM wp_cpt_combined 
WHERE FIND_IN_SET ('72', REPLACE(REPLACE(habitat_type, '[', ''), ']', ''))