MS SQL SERVER FTS FREETEXTTABLE search by part of word

Question

I use FTS and it returns for query "Møreforsking" results contains "Møre" as a separate word. I understand that "Møre" and "forsking" are different words and can be use separatly. How can I avoid the problem? I want to search only for whole word in described case.

Answer 1

Im not sure what codepage you are working in so I can't easily test it (SELECT 'language_id' AS "LCID" FROM sys.fulltext_index_columns;); if the word breaker is splitting the words up and considering moreforsking as two words for matching. You can test word breakers using

SELECT * FROM sys.dm_fts_parser (' "Møreforsking" ', {YOURCODEPAGE}, 0, 0)

if that is returning two words More and forsking then you need to tell FTS about your word Moreforking. You can create your own custom dictionary and add the word in if it is missing. The linked microsoft technet is a little confusing on this as it says follow the Sharepoint instructions but save in a sql folder.

The basics are

1. Create a file for your bespoke words (read the article to get the code pages right)
2. The file is just a list of words; max one word one per line

3. Save the file into your SQL Instance (sometimes its like this)

   C:\Program Files\Microsoft SQL Server\\MSSQL\Binn

4. get FTS to load the dictionaries by restarting the FTS daemon.

   exec sp_fulltext_service 'restart_all_fdhosts'

The bad news is you will need to reindex for it to take effect; so I would try it in test and read the share point article if you have a LOT of data that is indexed.