Fulltext search not returning expected results

Question

I am experimenting with SQL Server full text search.

I have a simple Categories table with Id as the primary key:

CREATE TABLE [dbo].[Category](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [CategoryName] [varchar](100) NOT NULL,
)

My Query is:

SELECT * 
FROM
 FREETEXTTABLE (Category, CategoryName, 'music') AS F
 INNER JOIN  Category C ON F.[Key] = C.Id
ORDER BY F.Rank DESC

This returns me several records with the word music in them, but it does NOT return any record with word ‘musical’.

Although, it can be said that the string being searched is not very big and using the LIKE operator will resolve it. I would like to use fulltext search because this simple example is going to be extended to include other tables and fileds.

Answer 1

If you want to match prefixes, you could use containstable instead of freetexttable and include a wildcard on your search term: 'music*'