Apr 17, 2014

Search or filter matching synonyms of word sql server full text search

Sometime we need that a filter condition should be also matched with the synonyms of given character in the sql server. We can achieve this goal by using sql server full text search index. For example:

SELECT *
FROM tblResume
WHERE CONTAINS(vcResume,'Beautiful')

But we need that vcResume  which contains any synonyms of word Beautiful should also included in the result set. In word word if vcResume contains worlds:

Synonym of Beautiful
Attractive
Pretty
Lovely
Stunning

Then it will be called as matching row.
Solution:
Step 1: Open the thesaurus file of English language in the notepad.  Its default path is:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER\MSSQL\FTData\ tsenu.xml

Note: There is very similar thesaurus file is tseng.xml but for English use tsenu.xml

Step 2: Replace or append the content of file by:

<XML ID="Microsoft Search Thesaurus">
   <thesaurus xmlns="x-schema:tsSchema.xml">
     <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            <sub>Beautiful</sub>
            <sub>Attractive</sub>
            <sub>Pretty</sub>
            <sub>Lovely</sub>
            <sub>Stunning</sub>
        </expansion>
    </thesaurus>
</XML>

And save the file.

Step 3:  Execute following script to load tsenu.xml:

EXEC sys.sp_fulltext_load_thesaurus_file 1033

Step 4: Modify your sql scipt:

SELECT *
FROM tblResume

WHERE CONTAINS(vcResume,'FORMSOF(THESAURUS,"Beautiful")')

No comments:

Post a Comment