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:

  1. Create the custom stoplist.
  2. Bind the full-text catalog to a custom stoplist.

    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.

  3. Creating a Custom Stoplist

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

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.
Copy 
USE <DBName>
        GO
        CREATE FULLTEXT STOPLIST [StopList]
        FROM SYSTEM STOPLIST;
        ALTER FULLTEXT STOPLIST StopList ADD 'search' LANGUAGE 'English';
        ALTER FULLTEXT STOPLIST StopList 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.
Copy 
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 StopList') 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