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.

Any luck with this? I'm having similar problems. – Johnny Oshika Nov 21 '13 at 23:15 — Johnny Oshika, Nov 21 '13 at 23:15

score 4 · Answer 1 · answered Nov 05 '13 at 20:54

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>

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. — GriGrim, Nov 06 '13 at 07:39

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

1 Answers1