Re: Help with WHERE string searching
H,
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
Why not split this into two fields:
pronoun enum('I','WE')
gender enum('MAN','WOMAN')
OR a SET
title SET(I,WE,MAN,MEN,WOMAN,WOMEN)
...and then get your application to parse the various sentences and
store the data correctly.
(mysql,query)
DSL
--
Con te partiro, su navi per mari
Che io lo so, no, no non esistono piu
Con te io li vivro.
(Sartori F, Quarantotto E)
-
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
Re: Help with WHERE string searching
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
Help with WHERE string searching
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 __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.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
Re: Help with WHERE string searching
Hi, Rob, Easily resolved. Use 'not like' in conjunction with 'like': WHERE ((title like '%man%') or (title like '%men%')) and title not like '%woman%' and title not like '%women%' Does that work? HTH! Jed On the threshold of genius, 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 __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.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 - 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
Re: Help with WHERE string searching
Hi Rob. Give this a shot. SELECT * FROM YourTable WHERE title LIKE '% Man' Or title LIKE '% Men' J. Ptak That comes very close to working for everything. Only thing is the word can be at the beginning of the title, the middle of the title, or the end. WHERE (title LIKE '% man') or (title LIKE '% men') or (title LIKE 'man %') or (title LIKE 'men %') or (title LIKE '% man %') or (title LIKE '% men %') That seems to work correctly. Thanks for the clue. I was hoping for something that would be quicker but if it works I'll use it. If you come across some way to use RLIKE or something similiar.. Thanks again, Robert --- Gate2Savings [EMAIL PROTECTED] wrote: Hi Rob. Give this a shot. SELECT * FROM YourTable WHERE title LIKE '% Man' Or title LIKE '% Men' J. Ptak Rob Gambit [EMAIL PROTECTED] wrote in message news:albosu$1dpj$[EMAIL PROTECTED]... 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 __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.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 __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.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
Re: Help with WHERE string searching
I see. What about something like this, then: Where title=VAR or left(title,length(VAR)+1)=concat(VAR, ) or right(title,length(VAR)+1)=concat( ,VAR) or title like % VAR % Does that one work? Jed On the threshold of genius, Rob Gambit wrote: WHERE ((title like '%man%') or (title like '%men%')) and title not like '%woman%' and title not like '%women%' Does that work? HTH! No, but I forgot to mention that I don't know what the keywords are before hand. They will be typed in by the user. Like if the user types in a keyword of cat I want to only return titles with the word cat but not cats or catalog I had thought to search for '% cat %' but the keyword could be at the beginning of the title, or the end, be followed by a . or a space. *shrug* probably need to think up something with RLIKE maybe. --- Jed Verity [EMAIL PROTECTED] wrote: Hi, Rob, Easily resolved. Use 'not like' in conjunction with 'like': WHERE ((title like '%man%') or (title like '%men%')) and title not like '%woman%' and title not like '%women%' Does that work? HTH! Jed On the threshold of genius, 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 __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.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 - 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 __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.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
