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:

Words connected with the AND binary operator must be found both in a document, their order or distance is not relevant.
One of the words connected with the OR binary operator must be found in a document, no matter which one (or even both).
A word prefaced with the NOT unary operator must not occur in a document.
Words connected with the NEAR binary operator must be found both in a document and they have to be close to each other (only 5 other words may be between the two).
Words enclosed in parenthesis form a phrase and must be all found in a document in the specified order.
If the equal sign is used before the searched word, only those documents are searched that contain this word written exactly, in the same case that is specified.

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)

Where:

ft_system_designation is a string in the form of application_name.fulltext_object_name
doc_id_column is the name of the column used for unique record identification
searched_words is the word or phrase that is searched for

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.

Example:

SELECT * FROM Txtab 
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:

The word is in uppercase.
The first letter is in uppercase, but some of the other letters are in lowercase.
The first letter is in lowercase.

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.

Example:

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:

SELECT ...., @@FULLTEXT_WEIGHT FROM ...  
WHERE Fulltext(...) 
ORDER BY @@FULLTEXT_WEIGHT

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:

You will enter a phrase and press the Search button.
You can eventually select the document source in the second field; by default, all sources from fulltext definition are selected (document source means each indexed column from each document table; the number of these sources thus equals to the number of filled-in rows in the grid in Fulltext Designer). It is assumed that each column has not its own unique ID that is why the resulting query can be created as a UNION of document sources. If each column has not its ID, some contexts will display incorrectly. In case of uncertainty, it is possible to enable SQL command trace and view the interrogated query.
The result is found in the bottom part of the grid: Each row corresponds to one occurrence; listed are: unique document ID, its context and source table name.

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.

Fulltext Systems - Indexing

Fulltext in 602SQL

Words that are not Indexed