asked on

Custom proximity search not working

This is driving me around the bend

I have a view called FulltextDiary with a Full-text index defined on it. One of the rows contains the following:

Hi fiona,

2 new real estate offers match the following search criteria:

[...]

I perform the following searches

SELECT *
  FROM CONTAINSTABLE([dbo].[FulltextDiary], [TEXT1], 'NEAR((new, offers), 20)')

Open in new window

SELECT *
  FROM CONTAINSTABLE([dbo].[FulltextDiary], [TEXT1], 'NEAR((new, match), 20)')

Open in new window

SELECT *
  FROM CONTAINSTABLE([dbo].[FulltextDiary], [TEXT1], 'NEAR((new, criteria), 20)')

Open in new window

SELECT *
  FROM CONTAINSTABLE([dbo].[FulltextDiary], [TEXT1], 'NEAR((real, criteria), 20)')

Open in new window

Happy days. Every one of these returns the above row. Then I execute:

SELECT *
  FROM CONTAINSTABLE([dbo].[FulltextDiary], [TEXT1], 'NEAR((fiona, offers), 20)')

Open in new window

Nothing. Zilch. Nada. Rien. Sweet Fanny Adams.

Why?

Member_2_1348041

ASKER

It gets worse. Another row contains:

What is that builder on about?

From: Giuseppe Carafa
[...]

When I try

SELECT *
  FROM CONTAINSTABLE([dbo].[FulltextDiary], [TEXT1], 'NEAR((What, about), 20)')

Open in new window

I get nothin'.

And it gets WORSE:

woot

From: Giuseppe Carafa
Sent: 12 June 2015 13:07
[etc...]

SELECT *
  FROM CONTAINSTABLE([dbo].[FulltextDiary], [DocumentText], 'NEAR((woot, carafa), 50)')

Open in new window

Returns nothing.

SELECT *
  FROM CONTAINSTABLE([dbo].[FulltextDiary], [DocumentText], 'NEAR((woot, carafa), 200)')

Open in new window

DOES return the row. Now I know that there are some new lines and things like that in the text, but I cannot see how there would be more than 50 terms between "woot" and "carafa" in the above text.

ASKER CERTIFIED SOLUTION

Member_2_1348041

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_1348041

ASKER

Found solution myself