Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (A-D)

 

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

See also
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';