SQL Server full-text search using thesaurus and prefix forms

Question

Here is an example full-table lookup table in the FTSdata field:

 CREATE TABLE dbo.tTest (Id INT, FTSdata VARCHAR(100)); INSERT INTO dbo.tTest (Id, FTSdata) VALUES (1, 'foo WordA'), (2, 'foo WordAaabbb'), (3, 'WordB bar'), (4, 'WordBbbaaa bar'); 

I would like to find all these entries regardless of whether users typed “WordA” or “WordB”.

My thesaurus looks like this:

 <expansion> <sub>WordA</sub> <sub>WordB</sub> </expansion> 

I need something like

 SELECT * FROM dbo.tTest WHERE CONTAINS(FTSdata, 'FORMSOF (THESAURUS, "WordA*")'); 

But, unfortunately, the asterisk is not supported in the FORMSOF predicate.

Answer 1

Verily, you cannot use * with THESAURUS, but you can do something like this.
Query

  SELECT FTSdata FROM dbo.tTest2 WHERE CONTAINS(FTSdata, 'FORMSOF (THESAURUS, Word) OR "Word*"') 

This will return any thesaurus that you define for the word "Word", as well as any words starting with "Word"

The reuslt set that I will return using this query is as follows:
Result set

 FTSdata foo WordA foo WordAaabbb WordB bar WordBbbaaa bar 

FTS XML FILE

 <expansion> <sub>Word</sub> <sub>WordA</sub> <sub>WordB</sub> </expansion>