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:

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).

Notes

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.