SQL Server 2008 full-text search doesn't find word in words?
- by Martijn
In the database I have a field with a .mht file. I want to use FTS to search in this document. I got this working, but I'm not satisfied with the result. For example (sorry it's in dutch, but I think you get my point) I will use 2 words: zieken and ziekenhuis. As you can see, the phrase 'zieken' is in the word 'ziekenhuis'.
When I search on 'ziekenhuis' I get about 20 results. When I search on 'zieken' I get 7 results. How is this possible? I mean, why doesn't the FTS resturn the minimal results which I get from 'ziekenhuis'?
Here's the query I use:
SELECT DISTINCT
d.DocID 'Id',
d.Titel,
(SELECT afbeeldinglokatie FROM tbl_Afbeelding WHERE soort = 'beleid') as Pic,
'belDoc' as DocType
FROM docs d
JOIN kpl_Document_Lokatie dl ON d.DocID = dl.DocID
JOIN HandboekLokaties hb ON dl.LokatieID = hb.LokatieID
WHERE hb.InstellingID = @instellingId
AND (
FREETEXT(d.Doel, @searchstring)
OR FREETEXT(d.Toepassingsgebied, @searchstring)
OR FREETEXT(d.HtmlDocument, @searchstring)
OR FREETEXT (d.extraTabblad, @searchstring)
)
AND d.StatusID NOT IN( 1, 5)
