List:General Discussion« Previous MessageNext Message »
From:Peter Lovatt Date:November 4 2002 11:48am
Subject:RE: select using regexp
View as plain text  
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

---------------------------------------------------------------------
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 <mysql-thread123916@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-peter=sunmaia.net@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


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