SQL server freetexttable partial words

Question

I'm using SQL free text searching for the first time and I'm a little confused why it behaves the way it does.

I have a table of contacts which users can search and I'm using

SELECT Contacts.*, [Rank]
FROM 
FREETEXTTABLE (Contacts, *, 'O''Roarty') AS Contact1_ftt
JOIN Contacts ON Contacts.ContactID = Contact1_ftt.[Key]

which returns 2 results on people with "O'Roarty" as their surname. However if I search for "Roarty" I get no results at all. The same happens if I search for "Dave" I get results but "Dav" no results.

Its looks like partial words don't work. Is there a way to enable partial word matches as well?

Answer 1

First thing Full-Text search does not support suffix search. So you cannot search using "*ave". Second regarding the uses of LIKE, LIKE AND FTS works differently. Look here for more detail.

Answer 2

This MSDN Question/Answer better explains the functionality than I can, so I'll just link it here.

You basically want to be using CONTAINSTABLE if you want partial word matches.