contact contact

FTS Search Syntax

  FTS indexes are searched with DBFIND. The DBFIND item parameter specifies the FTS index and the argument parameter the query.
DBFIND (db, set, mode, status, item, argument)
The DBFIND search argument consists of keywords (or ranges), separated by operators. A leading operator relates the search to previous search results.
[op] word [[op] word...]
  • word is a keyword or a range. Words are separated by operators. Double quotes may be used to designate a literal word.
  • and op indicates a boolean operator
For the image3k DBFIND the search argument must be terminated by either a semicolon or a NUL character.

The keywords are searched in the FTS dictionary index and operators define the relationship of the keywords. For example:

mike AND wuppertal
This consists of the keywords mike and wuppertal. As the boolean AND operator is used only records qualify having both mike and wuppertal.

Keywords (and operators) are case insensitive by default, except for keywords that belong to an FTS index designated specifically to be case sensitive.


A range may be specified by providing a minimum value, the range operator (:) and a maximum value. Either the minimum value or the maximum value may be omitted to specify an open range.

For example,

A:B, 10:15, X:
indicates all words between A and B (inclusive), any values between 10 and 15 or any words larger than X.

Pattern matching and generic words

Partial words, such as MAN@ (matching MANAGER, MANAGED, ...) may be used. In addition, match characters, such as "?" or "#" may be used.

For example:

MAN@ or AB#D

This matches any words that start with "MAN" or words that start with "AB", followed by a digit and the character "D".

Boolean operators

The boolean operators AND, OR and NOT may be used to define keyword relationships. For example,
mike AND NOT wuppertal
NOT test
would only qualify records with "mike" but not wuppertal. The second example would qualify any records NOT having the keyword test.

A space is considered an implied AND operator. Consequently, the following examples are equivalent:

mike AND wuppertal
mike wuppertal
The operator precedence is: ":" (range) > NOT > AND > OR
A:B NOT AB   -> (A:B) AND (NOT AB)
Parentheses may be used to explicitly specify the precedence of boolean expressions.

Syntax description


Keywords are words or ranges, separated by operators or spaces.

When searching numeric fields, only numbers (or numeric ranges) are valid. In numbers a dot (.) is understood as the decimal point.


Use double quotes around a keyword value to prevent it from being parsed. This is useful when searching for keywords that contain special characters or spaces or when a keyword could be interpreted as an operator (for example, "OR" in a search for a state code).


A keyword range may be specified by supplying a start value, the range operator and a stop value. Either the start value or the stop value may be omitted.

If both the start and stop values are present, any keywords between (and including) the specified values qualify.
If either the start or stop value is not present then a range specifies an open range qualifying any value from the start or up to the stop value.

For example:

The first example qualifies all keywords between "a" and "z", the second example qualifies any keywords between 2011 and 2012. The third example qualifies any keyword larger than or equal to 2011. The fourth example qualifies any keyword smaller than or equal to 2012.

Ranges can also use partial keywords by appending an at sign (@).

word:word   Range with start and stop value
>=word<=word   Same using relational operators to specify a range
:word   Open range with a stop value specified (<= relation)
<=word   Same using relational operator
word:   Open range with start value specified (>= relation)
>=word   Same using relational operator
a@:z@   Range using partial keywords
10:20   Numeric range

Relational operators

Relational operators are an alternate means for specifying ranges.

=  Equal to (redundant)
>=  Greater than or equal
>  Greater than
<=  Less than or equal
<  Less than

A relational operator is specified with the keyword (no separating space allowed). For example, >=A@ has the same meaning as A@: indicating an open range starting with A.

Two relational terms may also combined to specify a range with both a lower an upper boundary. For example, >=A@<=B@ is equivalent to A@:B@. Please note that no space is allowed between the two relational terms.

Pattern matching

dbfind supports the following wildcard characters anywhere in a character keyword argument:
  • ? - represents any single printable character
  • # - represents any single digit (0-9) of an ASCII number
  • @ - represents any number of ASCII characters, including spaces
In relational expressions, wildcards may only be used at the end of an argument string.

Soundex phonetic searches

To specify a phonetic or "Soundex" search on an FTS field add the Soundex operator ( ! ) to a keyword(s). For example, ALAN! AND CHRIS!.

If the FTS field does not support Soundex searches (the SX option must be specified for the FTS field) a non-zero database status is returned.
If a Soundex field is grouped with other fields, be sure to specify the Soundex field name in the field parameter when calling dbfind for a Soundex retrieval.

Applying search results to other sets

FTS search results may be applied to another set. In this case item values from the previous FTS search results are used as search criteria for the FTS field passed in the DBFIND item parameter.
& [item]
To use this function, an "&" character must be specified as the first character in the DBFIND argument parameter. An item name may be specified as an option.

The specified FTS field is then searched for item values obtained from a previous FTS search.

  • If the no item name is present, the master set search item is used implicitly. Consequently, this mode cannot be used for detail sets (unless the previous FTS search was on an aggregated FTS field).

  • If the optional item is present, the given FTS field is searched for values of the specified item. The FTS search results are consolidated if the item is in a master set to avoid duplicate values.
The target field (passed in the DBFIND item parameter) must be an FTS index in the specified data set.

Referencing previous search results

Previous search results may be refined with subsequent searches by starting the argument with a leading AND or OR operator. After performing an FTS DBFIND a leading AND or OR operator may be used to reference the previous search result. In this case the FTS search is applied to the previous FTS result. This allows to refine the search results, add to it or subtract from it by specifying additional keywords in Boolean operations.

A leading AND operator and any keywords that follow it intersects the previous and current search results. A leading OR operator and any keywords that follow it add the current search results. A leading AND NOT operator and any keywords that follow it filters the previous search results by the current search.

For example, an initial search could locate all customers in the state California. A subsequent search could then be used to remove any customers in San Francisco from the results.

To reference previous search results without further qualifying, place an asterisk ( * ) alone in the argument parameter.

When no records qualify

If a search does not qualify any records a database status 17 is returned. Even if all keywords in the search argument exist no entries may qualify based on the boolean conditions. For example,
mike AND wuppertal
does not qualify any entries if either keyword does not exist or do not apply to the same record (or the same master record for an aggregated FTS field).

By default, the FTS result is reset if no records are qualified and a subsequent DBGET will return an end of chain condition. As an option though, it is possible to retain previous FTS search results so a search may be re-tried by the user.

For the Eloquence language this behaviour is specified with the DBFIND mode. For the image3k DBFIND, DBCONTROL may be used to configure this behavior.

Undo previous search

After every FTS DBFIND (except those that qualify no records) the currently qualified and previously qualified results are saved in memory.

Two results are maintained:

  • The dbfind search result just performed
  • The dbfind search results that immediately preceded the search just performed
This allows to undo the most recent dbfind keyword search. For example, if an FTS search qualifies 100 customers initially and the result was subsequently limited to customers in Wuppertal it is possible to undo the last search and re-instate the previous result reflecting 100 customers. One may then retry the search with different arguments.

To back out of the most recent search, call DBFIND in "undo" mode.

Note that only the last search performed may be backed out. Any attempt to perform several undo operations in succession, will result in a non-zero database status.

  Privacy | Webmaster | Terms of use | Impressum Revision:  2012-12-17  
  Copyright © 2011 Marxmeier Software AG