Full text search in MS SQL server
  • 06 Apr 2022
  • 1 minute to read

Full text search in MS SQL server


Article Summary

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

VersionAvailable features

SQL Server 2014

https://docs.microsoft.com/en-us/sql/2014/getting-started/features-supported-by-the-editions-of-sql-server-2014?view=sql-server-2017

SQL Server 2016

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017

SQL Server 2017

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-2017

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

© Copyright 2024 Totara Learning Solutions. All rights reserved. Some content originally obtained via GPLv3 license and continues to be available under GPLv3. All other content is the sole copyright of Totara Learning Solutions. 


Was this article helpful?

Changing your password will log you out immediately. Use the new password to log back in.
First name must have atleast 2 characters. Numbers and special characters are not allowed.
Last name must have atleast 1 characters. Numbers and special characters are not allowed.
Enter a valid email
Enter a valid password
Your profile has been successfully updated.