Is it possible to combine an inflectional and proximity search in a single CONTAINSTABLE search condition?

I'm playing around in the AdventureWorks database with full text search. In the production.ProductDescription I am trying to search for "mountain" and "replacements".

I know there's a record with the following description:

High-performance mountain replacement wheel.

My first go around was this:

SELECT 
  pd.Description, 
  ct.RANK
FROM Production.ProductDescription AS pd 
  INNER JOIN CONTAINSTABLE(
    Production.ProductDescription, 
    Description, 
    'mountain NEAR replacements' 
  ) AS ct ON pd.ProductDescriptionID = ct.[KEY]
ORDER BY ct.RANK DESC;

This returned 0 rows. If I change 'mountain NEAR replacements' to 'mountain NEAR replacement' I get the record I expect in the resultant data set.

My next attempt was to try something like the following:

SELECT 
  pd.Description, 
  ct.RANK
FROM Production.ProductDescription AS pd 
  INNER JOIN CONTAINSTABLE(
    Production.ProductDescription, 
    Description, 
    'FORMSOF(INFLECTIONAL, "replacements") NEAR "mountain"' 
) AS ct ON pd.ProductDescriptionID = ct.[KEY]
ORDER BY ct.RANK DESC;

but this generates the error

Syntax error near 'NEAR' in the full-text search condition 'FORMSOF(INFLECTIONAL, "replacements") NEAR "mountain"'.

I had a look at the grammar for CONTAINSTABLE and it turns out that you can't have a generation_term (e.g. FORMSOF()) and a proximity term (e.g. NEAR) in the same search condition.

I added the following record to the table:

Replacement parts for you omg gee-whiz mountain

That record ranks high (96) in the following fts query:

SELECT 
  pd.Description, 
  ct.RANK
FROM Production.ProductDescription AS pd 
  INNER JOIN CONTAINSTABLE(
    Production.ProductDescription, 
    Description, 
    'FORMSOF(INFLECTIONAL,"replacements") AND "mountain"' 
  ) AS ct ON pd.ProductDescriptionID = ct.[KEY]     
ORDER BY ct.RANK DESC;

but as expected ranks lower (32) in this query:

SELECT 
  pd.Description, 
  ct.RANK
FROM Production.ProductDescription AS pd 
  INNER JOIN CONTAINSTABLE(
    Production.ProductDescription, 
    Descrip开发者_如何学编程tion, 
    '"replacement" NEAR "mountain"' 
) AS ct ON pd.ProductDescriptionID = ct.[KEY]
ORDER BY ct.RANK DESC;

For this contrived example, I'd like a user to be able to provide the search terms mountain and replacements and have it return records with a relatively high rank for records that contain replacement and mountain near each other but am at a loss at how to get there.

The last record you added "Replacement parts for you omg gee-whiz mountain"

The word replacement is not really near the word mountain, which is why the ranking is quite low.

In the Adventureworks example, you are looking at product descriptions which are really quite short fields and not really what the NEAR operator was intended for.

In this case I would just use

'FORMSOF(INFLECTIONAL, "replacement") AND FORMSOF(INFLECTIONAL, "mountain")' 

or a varation of that with your CONTAINSTABLE example.

SELECT  
  pd.Description,  
  ct.RANK 
FROM Production.ProductDescription AS pd  
  INNER JOIN CONTAINSTABLE( 
    Production.ProductDescription,  
    Description,  
    'FORMSOF(INFLECTIONAL,"replacements") AND FORMSOF(INFLECTIONAL,"mountain")'  
  ) AS ct ON pd.ProductDescriptionID = ct.[KEY]      
ORDER BY ct.RANK DESC; 

Interestingly, your query that you said gives you 96 ranking, only gives me a 32 ranking. I am using SQL Server 2005 to test and I set up the full text index on Product.ProductDescription.Description using defaults.

It's hard to say what exactly contributes to higher ranking here give that these very similar entries have quite different rankings: Replacement mountain wheel for entry-level rider. - 96 Replacement mountain wheel for the casual to serious rider. - 48