1

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?

  • From the documentation it seems possible, but you need to enclose the values in double quote, search for <prefix_term> in the linked page – Serpiton Jun 23 '14 at 12:05
  • I've missed that. Anyway, I tried the query but the problem is that it returns very distant words too. In fact even if I set the max distance to 1, the row is still returned. After examining the document I found out that the words I searched for were even 1 or 2 sentences distant away from each other. I guess that the engine doesn't treat "minist*" only in word boundaries. – Rosen Dimov Jun 23 '14 at 12:24
  • Sorry, my bad. It works perfectly fine. Thank you! I had missed the ( and ) before and after the list of words. – Rosen Dimov Jun 23 '14 at 15:18
  • As my previous comment seems to handle the question should I post it as an answer? – Serpiton Jun 23 '14 at 15:52
  • @RosenDimov - Does your comment mean that the wildcard search is working as you expected? – RLF Jun 23 '14 at 15:59
2

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

  • Thanks for the suggestion, but I knew about the CONTAINSTABLE option. The task I'm working on won't get any benefit from ranking (the rank isn't important at all), but the closeness of the words in the search is a requirement. And having in mind that, using CONTAINS would be better solution maybe, no joins are performed with another table. – Rosen Dimov Jun 23 '14 at 15:21
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.