Customizing Stopwords and Stoplists in SQL Server

CSM uses a default stoplist that ignores words like "the" or "an" when Users perform full-text searches. This improves search performance and ensures more meaningful search results.

To learn more about stopwords and stoplists in SQL Server, refer to SQL Server Stopwords and Stoplists.

You can create a custom stoplist that contains stopwords specific to your company's needs. The custom stoplist overrides the default stoplist provided with CSM. (Hosted customers: Contact Cherwell Support for information about custom stoplists.)

Follow this process to implement custom stoplists in SQL Server:

Create the custom stoplist.
Bind the full-text catalog to a custom stoplist.
Note: The use of SQL scripts is an advanced task typically performed by database administrators. The examples provided in this topic are intended to provide guidance for creating SQL scripts for your system only. Cherwell Software is not liable for changes made to your CSM system with SQL scripts.
Creating a Custom Stoplist

Use a SQL script similar to the following example to create a custom stoplist.

Important: Your custom stoplist must be named CherwellStopList.

The following example SQL script:

Creates a stoplist called CherwellStopList.
Adds the stopword "search" to the stoplist.
Removes the stopword "will" from the stoplist.

                         
USE <DBName>GO
CREATE FULLTEXT STOPLIST [CherwellStopList]
FROM SYSTEM STOPLIST;
ALTER FULLTEXT STOPLIST CherwellStopList ADD 'search' LANGUAGE 'English';
ALTER FULLTEXT STOPLIST CherwellStopList DROP '
will' LANGUAGE 'English';

Binding the Full-text Catalog to a Custom Stoplist

Use a SQL script similar to the following example to bind the full-text catalog to your custom stoplist. This process must only be run once; CSM will automatically bind to new and updated tables after the script is run once.

Important: For large databases, this process could take a significant amount of time and may impact search results during the rebuild.

The following example SQL script:

Creates and populates a temporary table that lists all tables using full-text search.
Alters each table to use the custom stoplist.

                            Use <DBName>go
--Create Temp Table for list of tables utilizing Full Text Search
Create table #FullTextTables(
TableName varchar(500))
--Populate the Temp Table 
Insert into #FullTextTables
select t.name as TableName
from sys.fulltext_indexes i,  sys.tables t
where i.object_id = t.object_id
--Perform Loop to alter each table to use a different StopList
Declare @TableName varchar(500)
select Top 1 @TableName = TableName from #FullTextTables order by TableName
While @TableName is not null
			begin
			exec ('ALTER FULLTEXT INDEX ON '+@TableName+'  Set StopList CherwellStopList')
			set @TableName = (Select min(TableName) from #FullTextTables where TableName > @TableName)
			end
--Rebuild the FullText Catalog to repopulate the Full Text Catalog with the new Stoplist
			ALTER FULLTEXT CATALOG [Trebuchet] REBUILD