473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Boolean Full-Text Searches

160 100+
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?

6 1946
Oralloy
985 Expert 512MB
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
waqasahmed996
160 100+
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
985 Expert 512MB
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
waqasahmed996
160 100+
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
985 Expert 512MB
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
waqasahmed996
160 100+
Thanks again. I will test and try to write best regular expression:)
Apr 13 '11 #7

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

Similar topics

2
by: WindAndWaves | last post by:
Hi Gurus What is the best way to store a boolean value in a MySql table (e.g. true or false) AND ( ;-) am I asking this in the right newsgroup (I could not find one for MySQL) Thank you -...
1
by: leegold | last post by:
Show full header Is there any way to make relevance when using boolean mode more useful? If not, are there plans in the Fulltext development "todo" for making it useful? I'm thinking of just...
1
by: Mike R | last post by:
I have a serializable class containing several bool fields. An XML string sent to me by another application contains "T" for true and "F" for false. XMLSerializer uses internally...
16
by: Ian Tuomi | last post by:
How can I define a boolean value in c? (an value that can only be either 1 or 0) I feel bad for the memory loss when declaring ints for variables that do not need that much memory. -- Ian Tuomi...
3
by: Lester Lee | last post by:
We're receiving the following error: "String was not recognized as a valid Boolean" whenever we click button1 then immediately click button2 before allowing the entire page to completely...
0
by: Robert Love | last post by:
I am trying to save some boolean values from checkboxes using isolated storage. I am able to do strings and integers without a problem but I can't work out how to save boolean values without seeing...
2
by: Lenn | last post by:
Hello, I am using reflections to get Parameters and their types of a COM Interop Assembly method. ParameterType.FullName returns "System.Boolean&" what's the significance of & at the end? ...
10
by: Zoe Hart | last post by:
I have a wsdl file that I received from a third party and I'm using wsdl.exe (.NET 2.0) to import it and generate a proxy class. I've actually got a proxy class that works, but I'm trying to...
0
by: jaywak | last post by:
Just tried running some code on Linux (2.4.21-32.0.1.EL and Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_04-b05)) and Windows XPSP2 (with Java HotSpot(TM) Client VM (build...
19
by: tshad | last post by:
I have a value in my sql table set to tinyint (can't set to bit). I am trying to move it into a boolean field in my program and have tried: isTrue = (int)dbReader and isTrue =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.