Fulltext query not seeming to use "and"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tshad - 15 Mar 2005 23:37 GMT

I have the following:

SELECT jobTitle from position where FreeText(jobDescription,'sales and
ccna') order by JobTitle DESC

This gives me 11 hits

If change change the FreeText clause to:

FreeText(jobDescription,'sales')

I get the same 11 hits.

But if I change it to:

FreeText(jobDescription,'ccna')

I get no hits.

I would have thought 'sales and ccna' would also have given me 0 hits.

What am I missing?

Thanks,

Tom

Reply to this Message

Hilary Cotter - 15 Mar 2005 23:54 GMT

You should use a boolean AND for this with Contains to get what you are
looking for.

select * from Contains(*,'"sales" and "ccna"')

FreeText will ignore the and unless it is removed from the noise word list,
in which case it will search on it. FreeText is a fuzzy search and not
exact. So it will rank rows which contain sales and ccna higher than rows
which contain only ccan or only sales.
.

>I have the following:
>
[quoted text clipped - 22 lines]
>
> Tom

Reply to this Message

tshad - 16 Mar 2005 00:25 GMT

> You should use a boolean AND for this with Contains to get what you are
> looking for.
[quoted text clipped - 5 lines]
> not exact. So it will rank rows which contain sales and ccna higher than
> rows which contain only ccan or only sales.

That was what I was looking for.

BTW you said that FreeText would rank the rows. Can you do that with
Contains? I would like to do this in some cases.

Thanks,

Tom

> .
>>I have the following:
[quoted text clipped - 23 lines]
>>
>> Tom

Reply to this Message

Hilary Cotter - 16 Mar 2005 00:27 GMT

actually I made a mistake, both FreeTextTable and ContainsTable will return
a rank value.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> > You should use a boolean AND for this with Contains to get what you are
> > looking for.
[quoted text clipped - 41 lines]
> >>
> >> Tom

Reply to this Message