5

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)'
rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • so in result set required does method occurs before apparatus in all scenarios? – Sanal Sunny Nov 02 '18 at 09:46
  • Yes, correct... – rory.ap Nov 02 '18 at 11:17
  • So, you don't want to use CHARINDEX for performance issues, right? – Taher A. Ghaleb Nov 03 '18 at 18:13
  • Not clear. Can you explain why method and apparatus should appear in that order only and WHY "This apparatus is used with these methods" is wrong ? Can you give few more such example ? – KumarHarsh Nov 05 '18 at 03:23
  • @KumarHarsh --- It should appear in that order if the person searching wants it to. I want my application to provide the ability to search that way. I don't really need a reason beyond that. There is not "wrong", it's just the way the client would be using the application. – rory.ap Nov 19 '18 at 12:49

1 Answers1

0

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.

Taher A. Ghaleb
  • 5,120
  • 5
  • 31
  • 44
  • Thank you for your answer. Unfortunately, as you pointed out, it's not efficient and thus not workable in my case. – rory.ap Nov 19 '18 at 12:53
  • Well, at least it is working according to the simple example you provided in your question. Can you please post a sample of your data where my queries do not work? With respect to efficiency, your question clearly asks for any way to do it (no efficiency concerns!) – Taher A. Ghaleb Nov 19 '18 at 14:23