[RESOLVED] MYSQL REGEXP: search a column for a term

[BillKat]

Hi folks,

I need some help with a MYSQL REGEXP, in searching for a term in a field. Need to build a SQL query, and I've struggled with REGEXP and my limited knowledge.
The word can be separated by a space or punctuation, slash, dash and underscore. If could occur on it's own in the column or at the start, end, or somewhere within it.

E.g. searching for "beer" I need to retrieve these kind of records:
Beer
Beer.Wine
Wine.Beer
Beer_Wine
Wine Beer Whiskey
Wine_Beer,Whiskey
Wine_Beer_Whiskey
Wine,Beer/Whiskey

But not retrieve these:
NobeerHere
MuchBeerage

SELECT product_name FROM products WHERE
product_name REGEXP '.*([[:space:]]|[[:punct:]])+{$searchterm}([[:punct:]]|[[:space:]])+.*'

Seems to partially work, but doesn't retrieve "Wine Beer Whiskey".
Also doesn't handle underscores but haven't tried to deal with them specifically yet.
Any help would be much appreciated...

[NogDog]

How about:

$sql = "SELECT product_name FROM products WHERE product_name 
REGEXP '(^|[[:space:][:punct:]]){$searchterm}($|[[:punct:][:space:]])'";

[BillKat]

Outstanding, thanks so much!
Don't totally understand it yet but will have another look later. The head's a bit toasted after going round in circles.

Thanks again....