Sql-server – Using wildcards in NEAR in a Full Text Search

full-text-searchsql server

Let's say I have the following text somewhere in a document stored in an FT indexed table in SQL Server 

minsitry of education and science

I want the query to ensure the words are NEAR, but the query won't contain the exact words (instead I would like to use wildcars) – something like

CONTAINS(FileContent, 'NEAR(minist*, educ*, 10, TRUE)')

If I try this query, no results will be returned as NEAR seems to work only with exact words.

My question – is there a way to complete a search like this using SQL Server queries?

Best Answer

Have you tried CONTAINSTABLE? This includes a Rank column so will give you more visibility of the ranking process. This worked for me:

SELECT * 
FROM dbo.yourTable t
    INNER JOIN CONTAINSTABLE(dbo.yourTable, FileContent, '"minis*" NEAR "educ*"') ct ON t.yourKey = ct.[Key]
ORDER BY RANK DESC

Update: found the following paragraph in the book Pro Full-Text Search in SQL Server 2008 by Michael Coles with Hilary Cotter

"...the NEAR operator is basically useless with the iFTS CONTAINS and FREETEXT predicates (it’s no different from using the AND operator). To get a true proximity search, you need to use CONTAINSTABLE or FREETEXTTABLE and restrict the results with a WHERE clause."

Ch3, p67

Related Question