ALTER TEXT CONFIGURATION statement

Alters a text configuration object.

Syntax

ALTER TEXT CONFIGURATION [ owner.]config-name
STOPLIST stoplist
| DROP STOPLIST
| { MINIMUM | MAXIMUM } TERM LENGTH integer
| TERM BREAKER { GENERIC | NGRAM }

stoplist : string-expression

Parameters

STOPLIST clause Use this clause to create or replace the list of terms to ignore when building a text index. Terms specified in this list are also ignored in a query. Separate stoplist terms with spaces. For example, STOPLIST 'because about therefore only'. Stoplist terms cannot contain whitespace.
Several ODBC samples are included with SQL Anywhere. You can find the samples in the samples-dir\SQLAnywhere subdirectories.

The Samples directory contains sample SQL code that loads stoplists for several languages. For the location of the Samples directory, see Samples directory.
DROP STOPLIST clause Use this clause to drop the stoplist for a text configuration object.
MINIMUM TERM LENGTH clause The minimum length, in characters, of a term to include in the text index. Terms that are shorter than this setting are ignored when building or refreshing the text index. The value of this option must be greater than 0. If you set this option to be higher than MAXIMUM TERM LENGTH, the value of MAXIMUM TERM LENGTH is automatically adjusted to be the same as the new MINIMUM TERM LENGTH value.
MAXIMUM TERM LENGTH clause The maximum length, in characters, of a term to include in the text index. Terms that are longer than this setting are ignored when building or refreshing the text index. The value of MAXIMUM TERM LENGTH must be less than or equal to 60. If you set this option to be lower than MINIMUM TERM LENGTH, the value of MINIMUM TERM LENGTH is automatically adjusted to be the same as the new MAXIMUM TERM LENGTH value.
TERM BREAKER clause The name of the algorithm to use for separating column values into terms. The choices are GENERIC (the default) or NGRAM. The GENERIC algorithm treats any string of one or more alphanumerics, separated by non-alphanumerics, as a term. The NGRAM algorithm breaks strings into n-grams. An n-gram is an n-character substring of a larger string. The NGRAM term breaker is required for fuzzy (approximate) matching, or for documents that do not use whitespace to separate terms. For more information about these algorithms and how to choose between them, see Altering text configuration objects.

Remarks

When you create a text configuration object, the settings are the same as that of the text configuration object you specified as a template during creation. Then, you use the ALTER TEXT CONFIGURATION statement to change setting options for the object.

Changing the default term length settings should only be done if the data in the columns used in the index justify it. That is, change the defaults only when they exclude terms that you need to search on, or when they include terms that you never want to search on.

Since text indexes are dependent on a text configuration object, you cannot alter a text configuration object if a text index refers to it unless the text index is empty. You must truncate the text index first, alter the text configuration object, and then refresh the text index. For an IMMEDIATE REFRESH text index that cannot be truncated, you must drop the index, alter the configuration, and re-create the index.

Note

If you create a text index but do not refresh it, you can still modify the text configuration object it depends on.

To determine the text indexes that refer to a text configuration object, see Accessing text index information in the database.

To view the settings for text configuration objects, query the SYSTEXTCONFIG system view. See SYSTEXTCONFIG system view.

Permissions

Must be the owner of the text configuration object, or have DBA authority.

Side effects

Automatic commit

Standards and compatibility

SQL/2003 Vendor extension.

Example

The following statements create a text configuration object, maxTerm16, and then changes the maximum term length to 16:

CREATE TEXT CONFIGURATION maxTerm16 FROM default_char; 
ALTER TEXT CONFIGURATION maxTerm16
   MAXIMUM TERM LENGTH 16;

The following statement adds a stoplist to the maxTerm16 configuration object:

ALTER TEXT CONFIGURATION maxTerm16
   STOPLIST 'because about therefore only';

Send feedback about this page via email or DocCommentXchange