SQL Server full-text search using thesaurus and prefix form together

Question

Here is a sample table with full-text search on 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 records no matter 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 asterisk is not supported in FORMSOF predicate.

Gamo

Any luck with this? I'm having similar problems. – Apfel

Answer 1

Its true 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 defined by you for the word "Word" and also any words starting with "Word"

The reuslt set I get back 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>

Dense

I want to get WordBbbaaa bar when I'm looking for WordA (not Word). This phrase is typed by user and I cannot crop received words. – Gamo