Re: Fulltext boolean search and the asterix
Paul DuBois wrote: At 10:49 +0100 11/8/05, Jigal van Hemert wrote: Lindsey wrote: but do you know how to use the * in regexp searches. err what i mean if i want to search for * and not use it as asterix? To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other. In this case, * is to be used as a special character, you don't want to match it literally. Paul, Lindsey wrote: i want to search for * and use it as asterix. I interpreted this as wanting to match the '*' literally... Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Fulltext boolean search and the asterix
At 10:49 +0100 11/8/05, Jigal van Hemert wrote: Lindsey wrote: ok thanks, then i know! but do you know how to use the * in regexp searches. err what i mean if i want to search for * and not use it as asterix? i have tried \* but that did't work, it just does the same as *. The manual comes again to the rescue ;-) Appendix G [1] tells us: To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other. So: SELECT 'GRANDS*N' REGEXP 'S\\*N'; - 1 [1] http://dev.mysql.com/doc/refman/5.0/en/regexp.html Um, no. :-) In this case, * is to be used as a special character, you don't want to match it literally. Also, * means any number of the previous thing, not any number of anything. The pattern you're looking for is .* (. = match any character, .* = match any number of any character). Also, if S must be at the beginning and Y at the end, you need to use ^ and $ to anchor the beginning and end of the match. So the pattern you want is '^S.*Y$'. mysql select 'SAMSUNG' regexp '^S.*Y$'; +---+ | 'SAMSUNG' regexp '^S.*Y$' | +---+ | 0 | +---+ 1 row in set (0.00 sec) mysql select 'SIEMENS' regexp '^S.*Y$'; +---+ | 'SIEMENS' regexp '^S.*Y$' | +---+ | 0 | +---+ 1 row in set (0.00 sec) mysql select 'SONY' regexp '^S.*Y$'; ++ | 'SONY' regexp '^S.*Y$' | ++ | 1 | ++ 1 row in set (0.00 sec) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext boolean search and the asterix
Lindsey wrote:
Lets say the table contains the following brands
SAMSUNG
SIEMENS
SONY
If you do a fulltext boolean search with the term:
-S*Y
-(S*Y)
everyting that starts with an S will be excluded... any solutions?
Although I couldn't find a question in your post, I guess you want to
know how to find anything that does not (start with 's' and ends with 'y').
Unfortunately the asterisk ('*') will not help, because the manual [1]
states:
The asterisk serves as the truncation operator. Unlike the other
operators, it should be *appended* to the word to be affected.
I don't think that full-text indexes were meant for this kind of
queries; although it will not use an index at all (so it might be a slow
query), using LIKE 'S%Y' or using a regular expression might be a
possible solution.
[1] http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Regards, Jigal.
smime.p7s
Description: S/MIME Cryptographic Signature
Re: Fulltext boolean search and the asterix
ok thanks, then i know!
but do you know how to use the * in regexp searches. err what i mean if i want
to search for * and not use it as asterix?
i have tried \* but that did't work, it just does the same as *.
Quoting Jigal van Hemert [EMAIL PROTECTED]:
Lindsey wrote:
Lets say the table contains the following brands
SAMSUNG
SIEMENS
SONY
If you do a fulltext boolean search with the term:
-S*Y
-(S*Y)
everyting that starts with an S will be excluded... any solutions?
Although I couldn't find a question in your post, I guess you want to
know how to find anything that does not (start with 's' and ends with
'y').
Unfortunately the asterisk ('*') will not help, because the manual [1]
states:
The asterisk serves as the truncation operator. Unlike the other
operators, it should be *appended* to the word to be affected.
I don't think that full-text indexes were meant for this kind of
queries; although it will not use an index at all (so it might be a slow
query), using LIKE 'S%Y' or using a regular expression might be a
possible solution.
[1] http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Regards, Jigal.
-
FREE E-MAIL IN 1 MINUTE!
- [EMAIL PROTECTED] - http://www.pc.nu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext boolean search and the asterix
Lindsey wrote: ok thanks, then i know! but do you know how to use the * in regexp searches. err what i mean if i want to search for * and not use it as asterix? i have tried \* but that did't work, it just does the same as *. The manual comes again to the rescue ;-) Appendix G [1] tells us: To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other. So: SELECT 'GRANDS*N' REGEXP 'S\\*N'; - 1 [1] http://dev.mysql.com/doc/refman/5.0/en/regexp.html Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
