Search code examples
sql-serverfull-text-searchthesaurus

Using a thesaurus with full text search is not working, possibly I am loading US english instead of British English

I've created a thesaurus file and loaded it as per the example.

I have a database with a table that is full text indexed.

I loaded it using 1033 which is 'English' according to the MSDN article here

It took a long time to load (4 minutes) it's pretty huge, so I know that it loaded it.

There is also a file for British english which is possibly what my SQL is using which would explain why it didn't work. However the number isn't listed on that chart so I do not know how to load it.

Assuming that's the problem, all is dandy. However I also can't find anything that suggests the thesaurus will simply work automatically on any free text query eg. CONTAINS so I don't know if I have to do something to my free text catalog (couldn't see anything, but you never know).

Any ideas?

Additional information:

In my tsenu.xml file:

<XML ID="Microsoft Search Thesaurus">

    <thesaurus xmlns="x-schema:tsSchema.xml">
    <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            <sub>John</sub>
            <sub>Jon</sub>
        </expansion>
    </thesaurus>

</XML>

I then ran this:

EXEC sys.sp_fulltext_load_thesaurus_file 1033;
select * from Cats where contains(CatName , 'Jon', language 1033)

There are records for 'Jon' and 'John' but the results are not using the thesaurus to show me alternatives.

Solution

  • try 

    WHERE CONTAINS(CatName , 'FORMSOF (THESAURUS, Jon)')

    using THESAURUS can be a bit tricky sometime, I havent used FTS for a long time but I remember sometimes , it will only return THESAURUS values not the actual value if it is contained in the data. for example if you are looking for thesaurus values for 'Jon' if there is a 'Jon' is data it will not return 'Jon' but will return all the Thesaurus for Jon, strange but this is how it works.

    if you are looking for two words is something like 

    WHERE CONTAINS(CatName , '"Word1" AND "Word2"')

    To search for John Smith I think you can do something like 

    WHERE CONTAINS(NAME, 'FORMSOF (THESAURUS, Jon) OR "Smith"')