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.

Dup: http://stackoverflow.com/questions/2716748/mysql-full-text-search-with-partial-words — Dan McGrath, Jun 03 '10 at 10:02
The link given is about full text search using MySQL database. My question is about SQL Server database. — Puneet, Jun 03 '10 at 16:00

score 2 · Accepted Answer · answered Jun 03 '10 at 14:03

2

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

answered Jun 03 '10 at 14:03

Joe Stefanelli

132,803
19
237
235

As per your suggestion, I tried this query SELECT * FROM CONTAINSTABLE (Category, CategoryName, 'music*') AS F INNER JOIN Category C ON F.[Key] = C.Id ORDER BY F.Rank DESC Gives the same result. Just the ranking is different – Puneet Jun 03 '10 at 16:00
My bad on the syntax. Try it as '"music*"' (That's single quotes on the outer, double quotes on the inner). – Joe Stefanelli Jun 03 '10 at 16:34

Fulltext search not returning expected results

1 Answers1