1

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.

D.

user142553
  • 55
  • 1
  • 7

2 Answers2

0
  • 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, '[', ''), ']', ''))
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
0

You can do it by removing the square bracket find in set works like Find_in_set(72,'72,73,74') it will return position of 72 i.e 1 while find_in_set(72,'73,72,74') i.e 2 so you can also try to use conditional operator find_in_set(72,'72,73')>0

shivam dhankani
  • 99
  • 1
  • 7