602SQL Documentation Index  

Document Fulltext Search

You can search for documents in a fulltext system containing certain words, phrases or combinations using the AND, OR, NOT and NEAR operators or using parenthesis. These rules apply for combining words you search for:

Search Examples

plant - Finds documents containing the word "plant".

"nuclear plant" - Finds documents containing the words "nuclear" "plant" immediately after one another.

coal AND "power plant" - Finds documents containing the words "coal" and the phrase "power plant".

"heating plant" OR "power plant" - Finds documents containing the phrases "heating plant" or "power plant".

"power plant" AND NOT water - Finds documents containing the phrase "power plant", but not containing the word "water".

"power plant" NEAR pollution - Finds documents containing the phrase "power plant" that occurs near the word "pollution".

The NOT operator can be used only for separate words. The AND operator has a higher priority than the OR operator (e.g. when searching for

aaa OR bbb AND ccc
you may find a document containing only the word aaa).

Fulltext Search in SQL Queries

The following predicate is used for searching:

Fulltext(ft_system_designation, doc_id_column, searched_words)


This predicate equals TRUE if the document of the specified number contains the specified words, or otherwise equals FALSE. This predicate can be used in search conditions in document tables.


WHERE Fulltext('RegisterOffice.correspondence', id_col, '(Linux OR ''Windows NT'') AND NOT MS-DOS')

Select records from the Txtab table that contain the word Linux or Windows NT and do not contain the word MS-DOS.

In lemmatized fulltext systems (such as Czech and Slovak) the searched words are usually transferred to their basic form, to search for all occurrences. Therefore:

...WHERE Fulltext('RegisterOffice.correspondence', doc_number, 'atomová elektrárna')
...WHERE Fulltext('RegisterOffice.correspondence', id_col, 'atomový elektrárna')
...WHERE Fulltext('RegisterOffice.correspondence', id_col, 'atomové elektrárny')
return the same result.

Distinguishing Case when Searching for Documents

The fulltext tools in 602SQL only allow the following cases:

A normal search does not check the word case. To obey the word case (considering the three cases), you must specify the equal sign in the last parameter of the predicate Fulltext in front of the searched word.


fat - Finds documents containing the word "fat" regardless of case.

=fat - Finds documents containing the word "fat" with the first letter in lowercase (general word, not at the beginning of a sentence).

=Fat - Finds documents containing the word "Fat" with the first letter in uppercase and some letters in lowercase (a general word at the beginning of a sentence).

=FAT - Finds documents containing the abbreviation "FAT" written in uppercase (a type of a file system; FAT = File Allocation Table).

Searching for a Context Occurrence

A fulltext system also allows you to search for the context of a specified phrase (words before and after the phrase in the original document). The function Fulltext_get_context using the @@FULLTEXT_POSITION system variable (INT type) provides the ability to find the context.

SELECT Fulltext_get_context('Literary.Correspondence',id_doc,'TXT',0,@@FULLTEXT_POSITION,1,5,'>>%<<')
FROM Doctab
WHERE Fulltext('Literary.Correspondence', id, '...')

Sorting Document Based on the Count and Weight of Occurrences

A fulltext system can keep track of the number of occurrences in a document. This count is generally used to sort the found documents. The sorted result is created by a query that uses the system variable@@FULLTEXT_WEIGHT in this form:

WHERE Fulltext(...) 

If the weight of individual word occurrences was enabled when creating the fulltext system, each occurrence of a word may be counted multiple times. Distinguishing weight depends on the document format and on the converting library's ability to assign different weights to words in various parts of the document.

The weight of occurrences is NOT used in the current version of 602SQL.

Negative Queries

It is not allowed to search for documents based on the fact that they do not contain some word or a phrase. You cannot search for documents that do not contain the word Windows using this predicate:

Fulltext('Literary.Correspondence', doc_number, 'NOT Windows')

The NOT clause must always be bound to an AND operator with a positive condition on some word or phrase search. You can do this:

Fulltext('Literary.Correspondence', doc_number, '''Operating system'' AND NOT Windows')

The fulltext system returns an empty result for a negative query. The NOT clause bound to another clause using the OR operator is ignored.

Test Fulltext Search in the 602SQL Client

A simple fulltext system front-end is built into the 602SQL Client. A phrase can be very simply specified at any time and you will get an information whether this phrase is contained in the fulltext, in which table it is and in which context.

The fulltext front-end window is invoked by clicking (or using the Run command from the menu) on the object of the Fulltext type in the Control Panel. A window containing three parts opens:

In case that the fulltext definition contains only a header without fulltext triggers (postponed manual indexing), you must select first in the dialog middle part the document table, column with ID for searching and column containing document text, format and mode for context display.