SQL Full Text Index search of partial keyword

Question

I have the following query to search product codes. If @keyword is BAG-61 then I receive no results, but if I change @keyword to BAG-612 then I do receive a result. Why is that?

Am I not supposed to get results with below query if only a partial piece of the product code is entered?

Same happens with SLAZ-816, if only SLAZ-81 is entered nothing is returned, but if SLAZ- is entered then SLAZ-816 is included in the results. Weird. Perhaps it's the way the index was set up?

DECLARE @search varchar(255)
SET @search = 'FORMSOF(INFLECTIONAL, "' + @keyword + '")'

SELECT p.code from Product p
where
(
    CONTAINS(p.code, @search) 
)

Answer 1

I don't think you are searching for a different tenses of a verb. FORMSOF(INFLECTIONAL is used to search for all the different tenses of a verb or both the singular and plural forms of a noun so it is not needed. Try this.

SELECT p.code from Product p
where
(
    CONTAINS(p.code, @keyword) 
)