Using wildcards in NEAR in a Full Text Search

Question

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?

Answer 1

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

Answer 2

Actually there is a way to wildcard full text search using both prefixes and suffixes combined with NEAR operator in SQL Server.

This is a little bit more expensive since it requires some preprocessing of the query. You can selecting the needed individual words from the full text index views sys.dm_fts_index_keywords or sys.dm_fts_index_keywords_by_document. After you have all the words you will need to use a CROSS JOIN to create the query.

Here is some sample code to show how it works:

CREATE TABLE dbo.TBL_NAME
   (ID INT NOT NULL, 
   field_name NVARCHAR(1000));

CREATE UNIQUE INDEX ui_id ON dbo.TBL_NAME(ID);
CREATE FULLTEXT CATALOG ft WITH ACCENT_SENSITIVITY=OFF AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.TBL_NAME(field_name) 
    KEY INDEX ui_id WITH (STOPLIST = OFF); -- Or SYSTEM, you choose
GO

INSERT INTO dbo.TBL_NAME VALUES(1,'The Ministry of Education is open.');
INSERT INTO dbo.TBL_NAME VALUES(2,'The ministerial activity of many departments result ' +
        'in several educators determining to retire.');
INSERT INTO dbo.TBL_NAME VALUES(3,'The ministerial activity of many departments result ' + 
        'in several exacting processes that are intended to prevent extra work by the educators.');

WAITFOR DELAY '00:00:05'; -- Give IFTS a few seconds to index.

-- Tables Created for resolving Wildcard names
DECLARE @FirstGroup TABLE (AGroup NVARCHAR(50));
DECLARE @SecondGroup TABLE (BGroup NVARCHAR(50));

INSERT INTO @FirstGroup 
  SELECT display_term 
  FROM sys.dm_fts_index_keywords (db_id('TEST'),object_id('TBL_NAME'))
  WHERE display_term like '%minist%';

INSERT INTO @SecondGroup 
  SELECT display_term 
  FROM sys.dm_fts_index_keywords (db_id('TEST'),object_id('TBL_NAME'))
  WHERE display_term like 'educ%';

-- Declare a search string that will OR the words that match your wildcards
DECLARE @SearchString NVARCHAR(2000);
SET @SearchString = ''

SELECT @SearchString = @SearchString + 'NEAR(('+ AGroup +','+BGroup+'),10, TRUE) OR '
FROM @FirstGroup CROSS JOIN @SecondGroup 
SELECT @SearchString = LEFT(@SearchString, LEN(@SearchString) - 3); -- Trim the last OR

-- This will only select the first two rows, since the last row exceeds
-- the distance limit
SELECT * FROM dbo.TBL_NAME
 WHERE CONTAINS(field_name,@SearchString)
GO

DROP FULLTEXT INDEX on dbo.TBL_NAME;
DROP FULLTEXT CATALOG ft;
DROP TABLE dbo.TBL_NAME; 
GO

Since SQL Server 2008 this has been supported by the two views mentioned above. If the overhead of collecting the needed words is not too expensive, it does open up new possibilities for Full Text searching.