Been spending time reading up on SQL Server 2005 FTS Thesaurus files here is what I've learned:
- You have to make your own Thesaurus file, can't seem to find one with common expansions.
- When you have finished your new file, you need to restart SQL 2005 (if it's 2008 there's a stored procedure to reload it "EXEC sys.sp_fulltext_load_thesaurus_file 1033;" 1033 for the language in this case ENU (US English).
- Make sure your search terms are not surrounded by double quotes because you thought they were necessary from a previous hacked together project using SQL FTS.
- Run tests and query comparisons to make sure it's working the way you think it should.
- Wonder about all the other predicates you can add to your search terms because you can't find a definitive list.
FREETEXT(<FieldNames>,formsof(thesaurus,<searchterms>))
FREETEXT(<FieldNames>,'"<SearchTerm_1>" NEAR "<SearchTerm_2>"')
FREETEXT(<FieldNames>,'isabout("<SearchTerm_1>" weight(<DecimalWeightValue_1>), <SearchTerm_2> weight(DecimalWeightValue_2>))')
FREETEXT(<FieldNames>, '<SearchTerm>', LANGUAGE <LanguageCode>) [for a list of language codes "select [name], alias, lcid from master.sys.syslanguages", use the lcid field] - If you're having problems getting the Thesaurus to appear to work, try using these to look at language settings
exec sp_configure 'default language'
SELECT @@language, @@langid
select [name], alias, lcid from master.sys.syslanguages - You may end up with lots of web pages open, maybe some of them are actually helpful:
http://www.mssqltips.com/tip.asp?tip=1491
http://msdn.microsoft.com/en-us/library/ms345187.aspx
http://msdn.microsoft.com/en-us/library/ms345186.aspx
http://www.mssqltips.com/tip.asp?tip=1353
http://www.mssqltips.com/tip.asp?tip=1342
http://www.mssqltips.com/tip.asp?tip=1332
http://www.ureader.com/msg/1147186.aspx
http://msdn.microsoft.com/en-us/library/cc280598.aspx
http://msdn.microsoft.com/en-us/library/ms176076.aspx
http://blogs.geekdojo.net/richard/archive/2006/09/01/13805.aspx (this one is funny as well, which can be nice after not finding anything that seems useful)
http://www.eggheadcafe.com/community/aspnet/13/10024815/need-help-on-sql-server-2.aspx (this one seems useful until you realize that there are no replies with answers)
http://arcanecode.com/2008/05/28/creating-custom-thesaurus-entries-in-sql-server-2005-and-2008-full-text-search/
http://arcanecode.com/2008/04/29/sql-server-full-text-search-the-fulltextcatalogproperty-function/
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/ - You finally get a sample working, then you realize you need to build a full list of all the Expansions and Substitutions you want.
No comments:
Post a Comment