I'd try using MySQL's regular expression functions:
Select .... where title regexp '[[:<:]]m[ae]n[[:>:]]'
or if you are generating this query programmatically, it might be
simpler to do something like
Select
....
where
title regexp '[[:<:]]man[[:>:]]' or
title regexp '[[:<:]]men[[:>:]]'
The [[:<:]]... [[:>:]] patterns match word boundaries, including
beginning- and end of lines. See
http://www.mysql.com/doc/en/Regexp.html
for more info.
You might also want to consider a fulltext index on the title:
http://www.mysql.com/doc/en/Fulltext_Search.html
-steve
At 7:38 PM -0700 9/6/02, Rob Gambit wrote:
>Hello MySQL mailing list.
>
>I am having trouble creating a SQL statement for
>searching.
>
>Suppose I have a field named title that contains one
>of these
>I am a Man
>I am a Woman
>We are Men
>We are Women
>
>Now I am trying to search that field using keywords,
>for example, I want to return any that contain the
>word "man" or "men" but not "woman" or "women"
>
>WHERE (title LIKE 'man') OR (title LIKE 'men')
>
>but that doesn't return anything. I tried
>
>WHERE (title LIKE '%man%') or (title like '%men%')
>
>but that returns everything. I tried using the _
>instead of % but the word may or may not be at the end
>or beginning of the line.
>
>Can someone point me in the right direction (or tell
>me how to do it)
>
>Thanks. Sorry for the newbie question.
>
>Robert
--
+------------------------------------------------------------------------+
| Steve Edberg [EMAIL PROTECTED] |
| University of California, Davis (530)754-9127 |
| Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| The end to politics as usual: |
| The Monster Raving Loony Party (http://www.omrlp.com/) |
+------------------------------------------------------------------------+
---------------------------------------------------------------------
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
