CONTAINS or FREETEXT

[DomTrix]

Not sure which to use and am finding the results they produce a little confusing.

They are being used to search fields in a database. The fields are title, author and publisher.

At present, using both freetext and contains, i put in a search for the title 'What on earth' and each time the results come back something like:

1. PEACE ON EARTH
...
5. WHAT ON EARTH

How could an exact match possibly come below only single word matches?

CORRECTION, I just rebuilt my FTI and the title 'WHAT ON EARTH' no longer appears in even the top 20 results . Confused am I!


Please could anyone give any advice on this (the MSDN documentation is somewhat confusing). I basically want slightly fuzzy searches on each field if that makes sense.

Thanks,

dt

 

[mrdenny]

How are you ordering the data? An exact match doesn't matter. The words "What" and "on" are being excluded as they are considdered noise words.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DomTrix]

Being ordered by Rank.

Ok the noise words makes sense although in searching for a title I would have thought noise words matter, especially when included in a phrase (as in this case). I guess Ill have to live with it.

Thanks

 

[mrdenny]

Nope, Noise words are removed from the query. Remove EARTH from the query and you'll see the error that says that all the words have been removed.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DomTrix]

Yeh, I realise that it ignores them, I just think that sometimes they are important, i.e. creating a ranked list for a phrase. But I'm sure there are good reasons.

Interestingly, if I put in "Sometimes the world is tough", I will get a match for a title 'Sometimes the world is tough', not suprising. If I put in "sometimes world tough", no match. Now I understand that that has to do with how I am building my search phrase (just wrapping the whole in double quotes) but it doesn't seem to fit in with the behaviour I described in my first post.

I may return to using freetexttable instead of containstable but it produces similarily strange results at times.

:/