Fulltext query not seeming to use "and"

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
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.
..
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:upqxO%23aKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>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
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OL0z5HbKFHA.436@.TK2MSFTNGP09.phx.gbl...
> 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.
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
> .
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:upqxO%23aKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>
|||actually I made a mistake, both FreeTextTable and ContainsTable will return
a rank value.
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
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O7wTZZbKFHA.3336@.TK2MSFTNGP09.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OL0z5HbKFHA.436@.TK2MSFTNGP09.phx.gbl...
> 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
>