# 18. Free Text Search

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Free Text Search
Basic Concepts
Creating Free Text Indexes
Querying Free Text Indexes
Text Triggers
Generated Tables and Internals
Removing A Text Index
Removing A Text Trigger
Internationalization & Unicode
Performance
Free Text Functions

## 18.1. Basic Concepts

A text index is created with the CREATE TEXT INDEX statement. This creates a number of stored procedures and triggers which will transparently manage the text index. A text index is dropped by dropping the generated words table, called <table>_<column>_WORDS, where <table> and <column> are the table and column over which the index is made.

Creating a Text Index

    CREATE TABLE FTT (ID INTEGER, FILE varchar,  DT LONG VARCHAR );
    CREATE TEXT INDEX ON FTT (DT);

This is the simplest case of making a text index. This process will add an extra column to the table being indexed which it will use to reference rows from the new text index. If there already exists an integer primary key then this will be used and no new column will be added. Such a column may not be 0 or negative.

Once the index is made the contains query can be used to retrieve rows:

    insert into ftt (id, dt) values (1, 'foo');
    select from ftt where contains (dt, 'foo');

The contains predicate is a normal SQL predicate and can be used together with other predicates in the where clause. Contains may however not figure inside an OR or NOT. Hence:

    select * from ftt where contains (dt, 'foo or bar ');

is OK but

    select * from ftt where contains (dt, 'foo ') or contains (dt, 'bar');

is not.

Previous Contents of Free Text Search | Chapter Contents | Next Creating Free Text Indexes