Full text search in MS SQL server

Full text search (FTS) allows a sophisticated approach to text search in Totara, particularly within the catalogue. Details of how to set this up using MS SQL can be found on this page.

Installation

Full text search is an optional component of the SQL Server Database Engine. If you didn't select Full text search when you installed SQL Server, run SQL Server setup again to add it.

More information is available in the Microsoft MSQL help documentation. You can also find a step-by-step guide for adding features to an instance of SQL Server in their help documentation.

The full text search component can be selected from the available features list when adding new features to the existing MSSQL server edition. Availability of the full text search component depends on the SQL server edition (see the table below).

Features supported by the SQL Server editions

Language

MS SQL Server uses English as its default language. You can see a list of options for other languages in the MSSQL full text languages documentation.

$CFG->dboptions['ftslanguage'] = 'English';
 $CFG->dboptions['ftslanguage'] = 'German';
 $CFG->dboptions['ftslanguage'] = 'Japanese';
 $CFG->dboptions['ftslanguage'] = 1028; // Traditional Chinese
 $CFG->dboptions['ftslanguage'] = 2052; // Simplified Chinese

Minimum search term length

MS SQL Server doesn't have specific default length requirements, and may find all of the words as long as they are words commonly found in a dictionary.

Stop words 

Stop words are a set of words that will be excluded from index and search queries. They depend on the language being used during indexing and search, as well as dictionary presence for database installation.

For example, if a user searches for 'Hotels in Wellington' then the system would exclude the word 'in' from the search.

More details on MS SQL Server 2017 stop words can be found in Microsoft's SQL documentation.