CREATE FULLTEXTINDEX Statement
|Previous Top Next|
Creates a new full-text index for an existing table.
CREATE FULLTEXTINDEX index_reference ON table_reference (column_reference [, column_reference ...])
DICTIONARY table_reference [CREATE] [UPDATE]
[SEPARATORS '<separating chars>']
A full-text index enables searching with full-text search-conditions like the CONTAINS predicate. The column references are a list of table columns of all types with the exception of blob columns including memos and wide memos. Full-text indexes need an additional database table, which contains the list of indexed words, the dictionary.
The dictionary table can be created by this statement or explicitly. If CREATE is not indicated, the statement expects an existing dictionary table with the following characteristics:
|•||First column is a VARCHAR or VARWCHAR of arbitrary length. This column determines the possible search-words. If words are longer than this column allows, they are cut.|
|•||Second column is of type BYTE. It contains the global relevance of this word.|
|•||Other columns may or may not follow according to the needs of the application.|
|•||There must be a column of type AUTOINC to identify the words. The indication of this AUTOINC column must be the first column of the table.|
If the CREATE clause is included, the statement creates a new dictionary table with a first column as VARCHAR(20).
If UPDATE is included, words that are not found in the dictionary table are added to it. If UPDATE is not included, only words from the dictionary table are indexed and can be found in searches.
If the SEPARATORS clause is included, you can define how the index splits text in words. For example, sometimes hyphens should be regarded as part of the word and sometimes as separators. The characters defined in <separating chars> replace the default separating chars, so you must indicate all desired separators including the obvious ones like space, comma, dot etc.
Full-text search technology for tables up to level three is different from the one for tables starting from level four. Only full-text indexes for tables of level four and above support automatic maintenance and relevance calculation.