List:General Discussion« Previous MessageNext Message »
From:Joseph Bueno Date:November 4 2002 3:22pm
Subject:Re: select using regexp
View as plain text  
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@stripped]
> > Sent: 04 November 2002 11:21
> > To: mysql@stripped
> > 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
> >
> > 



Thread
select using regexpMark Goodge4 Nov
  • RE: select using regexpAndy Eastham4 Nov
  • RE: select using regexpPeter Lovatt4 Nov
    • Re: select using regexpgerald_clark4 Nov
    • Re: select using regexpJoseph Bueno4 Nov