[SOLVED] Matching keywords within a comma delimited string

[zq29]

One of the fields in a MySQL table contains a comma delimted string of keywords, I'm trying to return rows where the terms in my query match any number of keywords in that string.

 

What I started out with was:

SELECT * FROM `table` WHERE `field` REGEXP ',?keyword,?'

Which doesnt really work as it matches combinations of characters within the whole string so would match 'cat' inside 'concatination' as one of the keywords in the string.

 

I guess what I need is something like this so that it matches keywords surrounded by either a comma, and/or the start/end of the string:

SELECT * FROM `table` WHERE `field` REGEXP '(^|,)?keyword(,|$)'

But this expression doesn't seem to work.

 

Any ideas?

[zq29]

Think I figured it out:

SELECT * FROM `table` WHERE `field` REGEXP '^(\w,)*keyword(,\w)*$'

Does that look right?

[effigy]

This should work; it's the same as you posted with the "?" removed:

 

SELECT * FROM `table` WHERE `field` REGEXP '(^|,)keyword(,|$)'

[zq29]

This should work; it's the same as you posted with the "?" removed:

 

SELECT * FROM `table` WHERE `field` REGEXP '(^|,)keyword(,|$)'

Ah hah, thanks Effigy Was still having troubles with my "think-I've-fixed-it" suggestion, until I realised \w isn't a word boundary in MySQLs RegExp engine. Your suggestion appears to be working brilliantly. Ta.