CODEWITHSUNDEEP
sql-serverfull-text-searchthesaurus
NibblyPig
NibblyPig

Reputation: 52952

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.

Upvotes: 1

Views: 1915

Answers (2)

M.Ali
M.Ali

Reputation: 69574

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"')

Upvotes: 4

jazakari
jazakari

Reputation: 66

Make sure to specify which language you want to use by using the optional language parameter when using Contains or FreeText

Contains and Free Text Information

I also looked at the chart and British English LCID is 2057.

Upvotes: 0

Related Questions