ALTER FULLTEXT STOPLIST (Transact-SQL)

Article
12/29/2022

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Inserts or deletes a stop word in the default full-text stoplist of the current database.

Syntax

ALTER FULLTEXT STOPLIST stoplist_name  
{   
        ADD [N] 'stopword' LANGUAGE language_term    
  | DROP   
    {  
        'stopword' LANGUAGE language_term   
      | ALL LANGUAGE language_term   
      | ALL  
     }  
;

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

Arguments

stoplist_name
Is the name of the stoplist being altered. stoplist_name can be a maximum of 128 characters.

' stopword '
Is a string that could be a word with linguistic meaning in the specified language or a token that does not have a linguistic meaning. stopword is limited to the maximum token length (64 characters). A stopword can be specified as a Unicode string.

LANGUAGE language_term
Specifies the language to associate with the stopword being added or dropped.

language_term can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of the language, as follows:

Format	Description
String	language_term corresponds to the alias column value in the sys.syslanguages (Transact-SQL) compatibility view. The string must be enclosed in single quotation marks, as in 'language_term'.
Integer	language_term is the LCID of the language.
Hexadecimal	language_term is 0x followed by the hexadecimal value of the LCID. The hexadecimal value must not exceed eight digits, including leading zeros. If the value is in double-byte character set (DBCS) format, SQL Server converts it to Unicode.

ADD 'stopword' LANGUAGE language_term
Adds a stop word to the stoplist for the language specified by LANGUAGE language_term.

If the specified combination of keyword and the LCID value of the language is not unique in the STOPLIST, an error is returned. If the LCID value does not correspond to a registered language, an error is generated.

DROP { 'stopword' LANGUAGE language_term | ALL LANGUAGE language_term | ALL }
Drops a stop word from the stop list.

' stopword ' LANGUAGE language_term
Drops the specified stop word for the language specified by language_term.

ALL LANGUAGE language_term
Drops all of the stop words for the language specified by language_term.

ALL
Drops all of the stop words in the stoplist.

Remarks

CREATE FULLTEXT STOPLIST is supported only for compatibility level 100 and higher. For compatibility levels 80 and 90, the system stoplist is always assigned to the database.

Permissions

To designate a stoplist as the default stoplist of the database requires ALTER DATABASE permission. To otherwise alter a stoplist requires being the stoplist owner or membership in the db_owner or db_ddladmin fixed database roles.

Examples

The following example alters a stoplist named CombinedFunctionWordList, adding the word 'en', first for Spanish and then for French.

ALTER FULLTEXT STOPLIST CombinedFunctionWordList ADD 'en' LANGUAGE 'Spanish';  
ALTER FULLTEXT STOPLIST CombinedFunctionWordList ADD 'en' LANGUAGE 'French';