TurboDB Engine Documentation

Full-Text Search

Previous  Top  Next

Full-text search is the search for an arbitrary word in a table row. This kind of search is especially useful for memo and wide memo fields, where searching with conventional operators and functions does not deliver the expected result or takes too long.

Full-text search in TurboDB has two restrictions:

There must be a full-text index on the table to use full-text searching capabilities.
One full-text search-condition always refers to a single table. (There can be multiple full-text search-conditions in the same where clause however.)

The basis of a full-text index is the dictionary, which is a normal database table with a certain schema. It holds the information on indexed words, excluded words, word relevance etc. Once the dictionary exists, it can be used for any number of full-text indexes on one or on multiple tables.

As of TurboDB 5, full-text search-conditions are embedded in the WHERE clause of the statement:

select * from SOFTWARE join VENDOR on SOFTWARE.VendorId = VENDOR.Id
where VENDOR.Country = 'USA' and (contains('office' in SOFTWARE.*) or contains('Minneapolis' in VENDOR.*))

A simple full-text search condition looks like this:

contains('office -microsoft' in SOFTWARE.*)

which is true, if any of the fields of the default full-text index of table SOFTWARE contains the word office but not the word microsoft. If the query refers to only one table, this can also be written as

contains('office -microsoft' in *)

If the full-text search-expression contains more than one word without the hyphen, TurboDB searches for rows that contain all the given words. Therefore

contains('office microsoft' in SOFTWARE.*)

will find rows, that contain both the word office and microsoft in any of the fields of the default full-text index of the table.

Words separated by a plus sign are searched for alternatively. The predicate

contains('office star + open' in SOFTWARE.*)

finds rows containing the word office plus either the word star or the word open (or both).

A full-text search-condition can be evaluated also on just a sub-set of the indexed columns:

contains('office' in SOFTWARE.Name, SOFTWARE.Category)

Will find all rows where the word office occurs either in column Name or column Category. If an additional column Description is also indexed, a row will not be returned if it only occurs in that column but not in Name or Category. In general, the full-text search-condition is evaluated on the union of indicated columns. So if there is a excluding term in it, like in

contains('office -microsoft' in Name, Category)

the word microsoft must appear neither in Name nor in Category, if it appears in Description the row can nevertheless be part of the result set.


Full-text indexes can be created with the CREATE FULLTEXTINDEX TurboSQL statement, with one of the database management tools (like TurboDB Viewer) or with the appropriate functions of the respective TurboDB access components.

The full-text searching technology has changed between TurboDB level 3 and level 4 tables. The new implementation is much faster and allows for maintained full-text indexes as well as for row relevance. It is strongly recommended to use level 4 tables, when working with full-text searching capabilities. The old full-text search will eventually be removed.