MySQL - Where - search string - MATCH.

Question

Hi guys,

Quick question. I'm in a bit of a rush but if someone could quickly point me in the right direction I would be very very happy.

I have a field in the db, let's call it field_a which returns a string in the format "20,50,60,80" etc.

I wish to do a query which will search in this field to see if 20 exists.

Could I use MySQL MATCH or is there a better way?

Thank you!

Answer 1

The better way would be to save the data differently. With WHERE a LIKE '...' and MATCH/AGAINST (besides being fairly slow) you can't easily search for just "20"... If you search for "20" you'll get "200" too; if you search for ",20," you won't get "20, 50"

Answer 2

Just be careful you don't get a substring of what you actually want - for example LIKE '%20%' would also match '50,120,70'. If you're using MySQL, you might want to use REGEXP '[[:<:]]20[[:>:]]' - where the funny faces are word boundary markers that will respect break on beginning / end of string or commas so you shouldn't get any false positives.

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Answer 3

Use FIND_IN_SET:

WHERE FIND_IN_SET(20, field_a) != 0