By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,401 Members | 820 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,401 IT Pros & Developers. It's quick & easy.

Boolean Full-Text Searches

100+
P: 160
I have a problem in Full Text Search. Can anyone help me please.

In Full Text Search in boolean mode if we search

'apple*'
It will find Find rows that contain words such as apple, apples, applesauce, or applet.

if we search

'apple banana'

It will find rows that contain at least one of the two words.

Is it possible that search 'apple banana' will give result same as

Expand|Select|Wrap|Line Numbers
  1. Select * from table where filed LIKE 'apple banana%'
  2.  
means it should not get all record which has word apple OR banana whether it give only those results which has values like this

apple banana
apple banana mango
apple bananadates

one option is to use double quotes (")
but it will not give result 'apple bananadates'

please is there any solution that i find all above three record with full text search (match-against)
Apr 13 '11 #1

✓ answered by Oralloy

waqasahmed996,

I really don't know.

And, for some cases, a regular expression match may be preferable (or absolutely necessary) considering the limitations of LIKE.

Basically, the LIKE operator is a dumbed down regular expression, dating to the era when regular expressions matching was still new technology. Because of match definition, the LIKE operator is easy to code, easy to optimize, and can test in a single pass against a data value.

That said, I do not have mySQL available to test with, so I'm somewhat shooting blind.

I'd think that you can use a regular expression such as
Expand|Select|Wrap|Line Numbers
  1.   "\bapple.*\bbanana"  
to find a word starting with "apple" followed by a word starting with "banana". But this won't find the sequence of "banana" followed by "apple".

To accomplish that, you'd need an expression similar to
Expand|Select|Wrap|Line Numbers
  1.   "(\bapple.*\bbanana)|(\bbanana.*\bapple)"  
Unfortunately MySQL's web site is being a real pill right now, otherwise I'd research a little bit. Also, I'm not sure what degree of regular expressions MySQL supports, so I may be completely fouling you up.

Give it a whirl, and maybe we can iterate to a solution?

Share this Question
Share on Google+
6 Replies


Oralloy
Expert 100+
P: 983
waqasahmed996,

Why don't you try something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM table
  2.   WHERE (filed LIKE '%apple%)
  3.         OR (filed LIKE '%banana%')
Cheers!
Oralloy
Apr 13 '11 #2

100+
P: 160
Thanks a lot Oralloy for your message

yes that is an option. But i want to use full text search (match-against) because i read that it is fast than LIKE. isn't it?
Apr 13 '11 #3

Oralloy
Expert 100+
P: 983
waqasahmed996,

I really don't know.

And, for some cases, a regular expression match may be preferable (or absolutely necessary) considering the limitations of LIKE.

Basically, the LIKE operator is a dumbed down regular expression, dating to the era when regular expressions matching was still new technology. Because of match definition, the LIKE operator is easy to code, easy to optimize, and can test in a single pass against a data value.

That said, I do not have mySQL available to test with, so I'm somewhat shooting blind.

I'd think that you can use a regular expression such as
Expand|Select|Wrap|Line Numbers
  1.   "\bapple.*\bbanana"  
to find a word starting with "apple" followed by a word starting with "banana". But this won't find the sequence of "banana" followed by "apple".

To accomplish that, you'd need an expression similar to
Expand|Select|Wrap|Line Numbers
  1.   "(\bapple.*\bbanana)|(\bbanana.*\bapple)"  
Unfortunately MySQL's web site is being a real pill right now, otherwise I'd research a little bit. Also, I'm not sure what degree of regular expressions MySQL supports, so I may be completely fouling you up.

Give it a whirl, and maybe we can iterate to a solution?
Apr 13 '11 #4

100+
P: 160
No words to say thanks. I think Regular expression have to use here. let me check all this. Thanks a lot for your guidance. you really save my a lot of time. I will be back after finding exact solution.
Apr 13 '11 #5

Oralloy
Expert 100+
P: 983
waqasahmed996,

Just beware that there is a trade off in complexity. If you use the long expression I wrote, it has to re-process the expression, if it doesn't find "apple" in the first place - it will look for banana and then fail, because it can't find an apple. You might want to consider using two regular expressions bound at a higher level with "and".

I really don't know how much data you have to process, but nothing beats performance testing to help tune queries. If your list is short, or the data small, it might be moot.

Good Luck!
Apr 13 '11 #6

100+
P: 160
Thanks again. I will test and try to write best regular expression:)
Apr 13 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.