MySQL LIKE search (blech!) with RegEx?


Well-known member
Okay, lets say I have a table with the following rows:
1 - apat
2 - vio,apat,pat
3 - pat
4 - vio,apat
5 - pat,vio
6 - pat
Lets say I want to do a MySQL like search, to get only rows where the column contains "pat". I would do this:
SELECT * FROM table WHERE column LIKE '%pat%'
The problem here is that would return all 6 rows, since technically the word "apat" contains "pat". What I would like to do, is a search query where only rows 2, 3, 5 and 6 would be returned... since rows 1 and 4 contain "apat" and not "pat", I would prefer them excluded. What would be the best way to do this?

Is it possible to do a regex? Could I search for '[^\w]pat[^\w]'? The [^\w] class would match any character that is not a word character; which would include commas, as well as start and end positions.
Seems like a case for data normalization to me.

Storing ordered lists in a string where you want special filtering and ordering is going to be a pain in the butt, and probably won't scale that well. Why not store it in a secondary table where you can query what you want, and even set ordering or any other relevant data you have?

I mean, you can probably get it to work with a regular expression (or two - one inside your order clause), but that's really not the proper way.