SQL Server Full-Text Search: combining proximity term and thesaurus

• alberto.decaro

  SSC Rookie

  Points: 41

  More actions

  June 14, 2013 at 2:11 am

  #275046

  SCENARIO

  I am developing a full-text search over a products table.

  The full-text indexed field of th table contains a brief description of the product, the name of the category and the product code.

  I succeed in querying the table using the CONTAINS predicate with NEAR terms:

  /* @mySearchTerm is a generic proximity term such as '"hdd*"~"sata*""' */

  SELECT myFields

  FROM SEARCHABLE_PRODUCTS

  WHERE CONTAINS(myIndexedField, @mySearchTerm)

  Then I try to enhance the search, leveraging on the thesaurus feature in order to enable synonyms search. E.G. 'hard disk', 'hard disk drive', 'hdd'. So I have configured the thesaurus xml file, and I succeed in querying the table using the FORMSOF term.

  ISSUE

  I do not succeed in combining the NEAR and the FORMSOF terms in the CONTAINS predicate, such as:

  /* @mySearchTerm is a generic proximity term such as '"hdd*"~"sata*""' */

  SELECT myFields

  FROM SEARCHABLE_PRODUCTS

  WHERE CONTAINS(myIndexedField, ' FORMSOF (THESAURUS, @mySearchTerm) ')

  A syntax error is returned. As to the MSDN reference, it seems that it is not possible. Is there any solution or workaround?