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.
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With