CODEWITHSUNDEEP
sqlmysql
Flukey
Flukey

Reputation: 6555

MySQL - Where - search string - MATCH

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!

Upvotes: 0

Views: 215

Answers (3)

reko_t
reko_t

Reputation: 56430

Use FIND_IN_SET:

WHERE FIND_IN_SET(20, field_a) != 0

Upvotes: 2

makenai
makenai

Reputation: 463

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

Upvotes: 1

Andreas Bonini
Andreas Bonini

Reputation: 44742

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"

Upvotes: 3

Related Questions