August 22, 2011 at 9:06 am
We have a bizarre issue regarding the CONSTAINSTABLE function in SQL Server 2008 R2. We are attempting to search for the character string 'See' using the proximity search term 'NEAR' on a column that is full-text indexed. The exact query that currently returns no results is:
SELECT qs.IndividualID, qs.Details, t.Rank FROM dbo.FullTextTable qs
INNER JOIN CONTAINSTABLE(dbo.FullTextTable, Details, '"see*" NEAR AAA', LANGUAGE 'English')
AS t ON qs.IndividualID = t. ORDER BY t.Rank DESC
The value in the 'Details' column, which is the one that is full-text indexed, of the FullTextTable table that we are expecting to be returned is:
'AAA John See id:10'
If I change the value in the Details column to
'AAA John Doe id:10'
and perform the following query:
SELECT qs.IndividualID, qs.Details, t.Rank FROM dbo.FullTextTable qs
INNER JOIN CONTAINSTABLE(dbo.FullTextTable, Details, '"doe*" NEAR AAA', LANGUAGE 'English')
AS t ON qs.IndividualID = t. ORDER BY t.Rank DESC
the results are returned as expected.
Is there a known issue with searching for 'See' on a full-text indexed column when using the NEAR proximity search term?
Any guidance would be appreciated.
August 24, 2011 at 3:32 pm
In case anyone was curious, the issue here was that 'see' is a system stop word. A full list of system stop words can be obtained by running the following query:
select * from sys.fulltext_system_stopwords
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply