Near Clause usage

Two items:
Is there a way to limit the proximity of the two words in the NEAR clause to
less than 50 words in SQL Server 2005 when doing a Full Text search? I
believe the default is 50 words.
Also, I noticed when I do fulltext search over an xml type column I don't
get the exact words I m looking for within my NEAR clause:
Ex.
select DocumentId, DocumentName, LegislationDocument
FROM Documents as Docs
WHERE CONTAINS(LegislationDocument, 'house NEAR hold')
This query returns housekeeper, household, holdings, etc.
Is there a way to have it search for just the exact word? I did try putting
double quotes around each word in the NEAR clause and that didn't seem to
help.
Any thoughts would be appreciated ... thanks.
Unless you do some sort of charindex calculation there is no way to do
separation distance based proximity searches using SQL FTS.
By default the contains based search is exact. You should not get matches of
house to household. Which word breaker/language are you searching in?
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
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:96B6F403-8E88-4DA2-9429-904267F9E167@.microsoft.com...
> Two items:
> Is there a way to limit the proximity of the two words in the NEAR clause
> to
> less than 50 words in SQL Server 2005 when doing a Full Text search? I
> believe the default is 50 words.
> Also, I noticed when I do fulltext search over an xml type column I don't
> get the exact words I m looking for within my NEAR clause:
> Ex.
> select DocumentId, DocumentName, LegislationDocument
> FROM Documents as Docs
> WHERE CONTAINS(LegislationDocument, 'house NEAR hold')
> This query returns housekeeper, household, holdings, etc.
> Is there a way to have it search for just the exact word? I did try
> putting
> double quotes around each word in the NEAR clause and that didn't seem to
> help.
> Any thoughts would be appreciated ... thanks.
|||I am using the English word breaker/language on an XML data type field.
From your first statement is the proximity of the words using the NEAR
clause within a CONTAINS still 50 words by default like it was in SQL Server
2000?
Thanks for the response Hilary.
"Hilary Cotter" wrote:

> Unless you do some sort of charindex calculation there is no way to do
> separation distance based proximity searches using SQL FTS.
> By default the contains based search is exact. You should not get matches of
> house to household. Which word breaker/language are you searching in?
> --
> 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
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:96B6F403-8E88-4DA2-9429-904267F9E167@.microsoft.com...
>
>
|||For contains yes. For Freetext the rank drops off to 0, but you might still
get nears results up to 1300 or so word separation.
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
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:4F2649B2-26E0-439E-8959-1E7842990043@.microsoft.com...[vbcol=seagreen]
>I am using the English word breaker/language on an XML data type field.
> From your first statement is the proximity of the words using the NEAR
> clause within a CONTAINS still 50 words by default like it was in SQL
> Server
> 2000?
> Thanks for the response Hilary.
> "Hilary Cotter" wrote:
|||Thanks for the info, Hilary.
Do you any suggestions about the near clause not returning the exact words
being searched for?
"Hilary Cotter" wrote:

> For contains yes. For Freetext the rank drops off to 0, but you might still
> get nears results up to 1300 or so word separation.
> --
> 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
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:4F2649B2-26E0-439E-8959-1E7842990043@.microsoft.com...
>
>
|||I was doing some selects using contains and the NEAR clause against an xml
data type within SQL Server 2005. I was noticing that the words were more
than 50 words apart (some of the hits far more than fifty words) . Noise
words are not counted as part of the fifty words, correct?
"Hilary Cotter" wrote:

> For contains yes. For Freetext the rank drops off to 0, but you might still
> get nears results up to 1300 or so word separation.
> --
> 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
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:4F2649B2-26E0-439E-8959-1E7842990043@.microsoft.com...
>
>
|||Noise words are counted.
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
"BWind" <BWind@.discussions.microsoft.com> wrote in message
news:6B148265-4088-4DE5-ABD7-A5EC2E89EC3B@.microsoft.com...[vbcol=seagreen]
>I was doing some selects using contains and the NEAR clause against an xml
> data type within SQL Server 2005. I was noticing that the words were more
> than 50 words apart (some of the hits far more than fifty words) . Noise
> words are not counted as part of the fifty words, correct?
> "Hilary Cotter" wrote:
|||Thanks Hilary ...
Do have any thoughts on the words being farther than 50 words apart in my
NEAR clause using CONTAINS? Some of them are in the hundreds if I am counting
noise words. I thought I read that there are no configuration settings in SQL
Server 2005 for FTS to change this so I would be expecting a 50 word
separation. Could it be because I am FTS an XML document or am I possibly
doing something wrong?
Thanks again for all the responses!
"Hilary Cotter" wrote:

> Noise words are counted.
> --
> 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
> "BWind" <BWind@.discussions.microsoft.com> wrote in message
> news:6B148265-4088-4DE5-ABD7-A5EC2E89EC3B@.microsoft.com...
>
>