SQL Server 2008 full-text search doesn't find word in words?

Question

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)

Answer 1

I would suggest that you look at using the CONTAINS predicate, as opposed to FREETEXT

Usage scenarios, including what you wish to achieve, can be found in the examples section of the documentation.

From your description, I believe that you are attempting to perform a "Prefix" search. For example:

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "SearchTerm*" ');
GO

This will provide you a result set containing all words that "contain" the prefix search term. If you wish to search for a word anywhere within a term then use an asterisk(*) before the search term also.

Hoep this helps.