Match inflectional forms of words in a particular order with SQL Server full-text search

user_2704659 0
Viewed 390

I'd like to use SQL Server full-text search to find inflectional forms of words that occur in a specific order. So the words method and apparatus would match These are the methods I'm using with the apparatuses but not This apparatus is used with these methods.

Is there a way to do this? It seems pretty simple, but I've found nothing.

I've tried CONTAINS with:

'NEAR((method,apparatus), MAX, TRUE) AND FORMSOF(INFLECTIONAL,method) AND FORMSOF(INFLECTIONAL,apparatus)'

'FORMSOF(INFLECTIONAL,NEAR((method,apparatus), MAX, TRUE))'

'NEAR((FORMSOF(INFLECTIONAL,method),FORMSOF(INFLECTIONAL,apparatus)), MAX, TRUE)'
full-text-search sql-server-2016 sql-server
1 Answers

The problem is that you cannot combine FORMSOF with NEAR (here is the reference). A possible way to do (though not efficient) is to try all the different alternatives of 'method' and 'apparatus' (if you don't have other words to search for), like the following:

   SELECT some_id
   FROM some_table
   WHERE CONTAINS(some_text, 'NEAR((method,apparatus), MAX, TRUE) OR NEAR((method,apparatuses), MAX, TRUE) OR NEAR((methods,apparatus), MAX, TRUE) OR NEAR((methods,apparatuses), MAX, TRUE)')

Another option is to use CHARINDEX (which could also be inefficient), like this:

   SELECT some_id
   FROM some_table
   WHERE CONTAINS(some_text, 'FORMSOF(INFLECTIONAL,method) AND FORMSOF(INFLECTIONAL,apparatus)')
   AND CHARINDEX('method', some_text) < CHARINDEX('apparatus', some_text)

They both worked fine with me. Hope this helps.

user_33863100
Related