List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:September 7 2002 9:49am
Subject:Re: Help with WHERE string searching
View as plain text  
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                                      sbedberg@stripped |
| 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/) |
+------------------------------------------------------------------------+
Thread
Help with WHERE string searchingRob Gambit7 Sep
  • Re: Help with WHERE string searchingJed Verity7 Sep
  • Re: Help with WHERE string searchingSteve Edberg7 Sep
Re: Help with WHERE string searchingRob Gambit7 Sep
Re: Help with WHERE string searchingJed Verity7 Sep
  • Re: Help with WHERE string searchingDavid Lloyd7 Sep