Tip #39 – Full Text Search Indexes via SQL

The Advantage Full Text Search engine provides a robust mechanism for searching text and memo data within a table. Although it can be used without an index searches are much more efficient when an index has been created. The engine can also be used to search across multiple fields if an index is created. FTS indexes can only be created on text fields i.e. Character, CiCharacter, RAW, Memo, Binary, Image and VarChar.

Since full text search (FTS) indexes, also called content indexes, can only be created on a single field they can be created using a CREATE INDEX statement. You must use the CONTENT keyword to create a FTS index. You can specify various options such as Minimum Word Length, Maximum Word Length, Delimiters, Noise Words, Dropped Characters and Conditional Dropped Characters.

The following examples show usage of the CREATE INDEX statement.

// Create a basic FTS Index

CREATE INDEX Notes ON Orders ( NOTES ) CONTENT

// FTS index with options

CREATE INDEX NOTES ON ORDERS ( NOTES ) 

  CONTENT MIN WORD 3 MAX WORD 30

  DROPCHARS '''"`'

  CONDITIONALS ',.?!;:@#$%^&()-_'

  IN FILE "ORDERS.cdx";

// New keyword overwrites defaults

// only use " and ` as drop chars

CREATE INDEX NOTES ON ORDERS ( NOTES ) 

  CONTENT MIN WORD 3 MAX WORD 30

  NEW DROPCHARS '"`'