Hi, REGEXP is much more powerful than LIKE; you can match full words with this syntax:
SELECT * FROM TABLE WHERE field REGEXP "[[:<:]]cat[[:>:]]"; (Easy, isn't it ? ;) ) You can find more examples in the manual: http://www.mysql.com/doc/en/Regexp.html Regards, Joseph Bueno NetClub gerald_clark wrote: > There are too many exceptions for this to be usefull. > What about lines ending in cat. or cat, > Your example won't match them. > Perhaps "% cat.%" and "% cat,%" patterns might be more helpfull, > but what about lines that begin with cat? > > Peter Lovatt wrote: > > > Hi > > > > You could use either normal or fulltext searches. Regexp may well be a > > good > > answer (not used it myself). The following should also work > > > > SELECT * > > FROM table > > WHERE > > field LIKE "% cat %" > > OR field LIKE "% cat. %" > > OR field LIKE "% cat, %" > > > > (note the spaces to make sure you get only complete words) > > > > or if you have a mysql version that supports FULLTEXT > > > > SELECT * FROM table > > WHERE MATCH (field) AGAINST ('cat'); > > > > http://www.mysql.com/doc/en/Fulltext_Search.html > > > > which is much more elegant > > > > HTH > > > > Peter > > > > ----------------------------------------------- > > Excellence in internet and open source software > > ----------------------------------------------- > > Sunmaia > > Birmingham > > UK > > www.sunmaia.net > > tel. 0121-242-1473 > > International +44-121-242-1473 > > ----------------------------------------------- > > > > -----Original Message----- > > From: Mark Goodge [mailto:mark@;good-stuff.co.uk] > > Sent: 04 November 2002 11:21 > > To: [EMAIL PROTECTED] > > Subject: select using regexp > > > > > > Hi, > > > > I've got a problem that I'm hoping someone can help with. I need to do > > a query against a text column in order to extract entries that will > > match whole words only - for example, a search for "cat" should match > > any of: > > > > The cat sat on the mat > > It was a large cat. > > Cat food is interesting. > > Dog. Cat. Fish. > > > > but not match > > > > in a catatonic state > > it was a catastrophe > > scattergun approach > > > > It looks as if the MySQL REGEXP function is what I need here, but I > > can't work out from the documentation how to get what I want. > > > > Any suggestions? > > > > Mark > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
