SQL Server :- Full-text Search and Synonyms

Background

Full-text is a good way to look for matching text.

But, as names and languages evolve, it is a hit and miss.

Let us start addressing the misses by using Thesarus.

 

Thesarus

Outline

  1. Manage Thesarus File
    • File System
      • Identify SQL Server Full-text folder
    • Language Specific File
      • Review Language Specific Files
      • Identify Language Specific File
      • Backup File
      • Edit File
  2. Update Database
    • Identify Language ID
      • sys.languages
    • Parse & Load Thesarus Data File
      • sys.sp_fulltext_load_thesaurus_file

 

Tasks

Manage Thesarus File

File System

Identify SQL Server Full-text folder
SQL

select 

          [database]
            = tblSD.[name]
        
        , [filenameDBMasterData]
            = tblSMF.[physical_name]

        , [folderBase]
            = replace
                (
                    tblSMF.[physical_name]
                    , '\Data\master.mdf'
                    , ''
                )
        , [folderFullText]
            = replace
                (
                    tblSMF.[physical_name]
                    , '\Data\master.mdf'
                    , ''
                )
                + '\FTData'


from   sys.databases tblSD

inner join sys.master_files tblSMF

        on tblSD.database_id = tblSMF.database_id

where  tblSD.[name] = 'master'

and    tblSMF.[name] = 'master'

 

Output

Explanation
  1. Folder
    • Database Master – Data file
      • C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
    • SQL Server Instance Base  Folder
      • C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL
    • SQL Server Full Text Folder
      • C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\FTData

 

Language Specific File
Review FT Files

Identify Language Specific File

The file that we want for English is tseng.xml

Backup File

Please Backup entire folder or the specific files you will be editing

 

Edit File

Here we indicate that Abram, Abraham, and Ibrahim can be substituted for each other.

 

Update Database

Identify Language ID

sys.syslanguages
SQL

select 
          tblSL.[langid]
        , tblSL.[name]
        , tblSL.[alias]
        , tblSL.[lcid]
        , tblSL.[msglangid]

from   sys.syslanguages tblSL

where  (

            ( lower(tblSL.[alias]) like '%english%')

       )

Output

Parse & Load Thesarus Data File

sys.sp_fulltext_load_thesaurus_file
SQL


-- English
EXEC sys.sp_fulltext_load_thesaurus_file 1033;

-- British English
EXEC sys.sp_fulltext_load_thesaurus_file 2057;

Summary

Bro, this post is already long enough.

Trying it out and usage deserves its own post.

 

References

  1. Microsoft
    • Docs / SQL / Query data / Full-text search / Search / Thesaurus Files
      • Configure and Manage Thesaurus Files for Full-Text Search
        Link
    • Docs / SQL / Reference / System stored procedures / Full-Text Search & Semantic Search
      • sp_fulltext_load_thesaurus_file
        Link