Sql-server – SQL Server Full Text Search using CONTAINS, FORMSOF, NEAR for multiple search words

formsoffull-text-searchsql serversql-server-2005tsql

I am new to SQL Server Full Text Searching, and am trying to figure out the best way to search on multiple words using the inflectional engine so the search uses the various forms of all of the words.

From what I read, FREETEXT uses an implicit OR when used with multiple words. I want an AND so that the search results contain all of the words, so because of this I am choosing to use CONTAINS.

I am trying to do something like the query below, which uses FORMSOF with the proximity keyword NEAR for multiple words. Note that this is not valid syntax and returns an error:

select top 5 *
from content
WHERE CONTAINS((Title,Subtitle,Body), 'FORMSOF(INFLECTIONAL, model NEAR airplane)')

However, the query below works, but I don't know if it gives the intended results. Is there a difference between "AND" and "NEAR" with SQL Full Text Search?

select top 5 *
from content
WHERE CONTAINS((Title,Subtitle,Body), 'FORMSOF(INFLECTIONAL, model) AND FORMSOF(INFLECTIONAL, airplane)')

I guess what I am asking is, is there a way to use CONTAINS, FORMSOF, and NEAR with multiple search words? Or should I just use the second query above that uses "AND"?

Best Answer

From the docs:

<proximity_term> ::= 
     { <simple_term> | <prefix_term> } 
     { { NEAR | ~ }
     { <simple_term> | <prefix_term> } 
     } [ ...n ]

This means you can use NEAR predicate for (possible prefixed) words, phrases and their combinations.

Since your search terms are inflected using quite simple rules, you can just use prefixes:

SELECT  *
FROM    content
WHERE   CONTAINS((Title,Subtitle,Body), 'model* NEAR airplane*')

or use AND and do fine filtering on the client side

SELECT  *
FROM    ft
WHERE   CONTAINS((Title,Subtitle,Body), 'FORMSOF(INFLECTIONAL, "model") AND FORMSOF(INFLECTIONAL, "airplane")')
Related Topic