Full-Text Search - FORMSOF(INFLECTIONAL, word) with NEAR

Category: sql server search


RyDude on Wed, 13 Feb 2013 15:14:59

Is it possible to use FORMSOF(INFLECTIONAL, word) and NEAR together? For example, this is how I would expect the syntax to look, but it doesn't work:

select note from exampletable
where contains(note, 'NEAR((FORMSOF(INFLECTIONAL, branch),FORMSOF(INFLECTIONAL, fell)), 5)')


SQLWork on Thu, 14 Feb 2013 22:35:18

I believe that this just exceeds the complexity that SQL Server can manage with NEAR. 

For example,  FORMSOF(INFLECTIONAL,branch) returns 4 different words and FORMSOF(INFLECTIONAL,fell) returns 8 different words.

The documentation on NEAR states that it works only with simple_term and prefix_term.  If you want to roll your own, you could use the sys.dm_fts_parser to collect all of the terms of each inflectional and cross join to build a large query string.   A simple example result could be:

select *  from TableWithTextToSearch
where contains(*, '"branch" NEAR "fall"')
   OR  contains(*, '"branch" NEAR "fell"')
   OR contains(*, '"branches" NEAR "fall"')
   OR contains(*, '"branches" NEAR "fell"')