MySQL LIKE search (blech!) with RegEx?

[Jaxel]

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.

 

[RobParker]

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

?

 

[Jaxel]

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

?

Yep, thats what I was looking for... thanks... but now I need some help with Regex...

Is there a way I could match: (in this order)

• comma OR beginning of string
• pat
• comma OR end of string

?

 

[Adrian Schneider]

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.

 

[Mike]

I agree about a separate lookup table - it's fine to cache data like this if appropriate but another table may be useful.

That said, what you really want for that data structure is FIND_IN_SET: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

 

[Jaxel]

FIND_IN_SET was exactly what I was looking for... thanks.