Progress
Database Design
Guide


Word Index Support

To support word indexes, the CONTAINS option is included in the WHERE clause of the Record phrase. This allows you to use a word index in a FOR EACH statement. For example, suppose you had defined a word index on the address field of the Customer table. The following statement finds customer records that include the word Homer within the address field:

FOR EACH customer WHERE address CONTAINS "Homer": 

This is the general syntax of the WHERE clause with the CONTAINS option:

SYNTAX
WHERE field CONTAINS string-expression 

field

Refers to a character field or array on which a word index has been defined.

string-expression

Contains one or more words to search for in the character field field:

SYNTAX
"word  [  [  &  |  |  |  !  |  ^  ]  word  ]  ... " 

word

A word to search for or a wildcard string. A wildcard string is any word that ends with an asterisk (*). You can use a wildcard string to search for words that begin with a specific initial substring. For example, the string “sales*” matches the words sales, salesman, and salesperson.

& | | | ! | ^

Word separators. The ampersand (&)represents a logical AND; a vertical line (|), exclamation point (!), or caret (^) represents a logical OR. Separators allows you to limit your search to records that contain all words that you specify or to enlarge your search to all records that contain any of the words you specify. You can combine AND and OR operations within a string expression and group items with parentheses to create complex search conditions.

EXAMPLE

The following example assumes that a word index has been defined on the Terms field of the order table:

FOR EACH order WHERE Terms
    CONTAINS "free | gratis | (no & charge)":
  DISPLAY order.
END. 

The AND operator (&) takes precedence over the OR operator (|). For this reason, the parentheses in the preceding example are not required. They are used only to improve readability.

EXAMPLE

This example finds all books whose description contains both the words “computer” and “science”. If you omit both the ampersand and vertical line between two words (that is, the words are separated only by spaces), a logical AND is assumed:

FOR EACH book WHERE description CONTAINS "computer science":
    DISPLAY book.
END. 

If your search condition specifies that the record must contain two or more specific words, the order of the words is insignificant. In the previous example, a record containing the word “science” and later the word “computer” may be displayed.

EXAMPLE

To find words in exact order, you can combine CONTAINS with the MATCHES function. In this example, the words “computer” and “science” must appear in exact order, although another word or words can appear between them:

FOR EACH book WHERE description
    CONTAINS "computer science" and description
      MATCHES "*computer *science*":
    DISPLAY book.
END. 

EXAMPLE

You can combine other search criteria with a CONTAINS option. This example searches a table that contains information about traffic accidents. It finds only records for accidents that occurred in Boston and contain the word “milk” and either the word “truck” or “trailer” in the description field:

FOR EACH accident WHERE city = "Boston" AND
        description CONTAINS "milk (truck ^ trailer)": 

NOTE: Use the Stash Area (-stsh) startup parameter when you have a word index on a large character field. For more information about this startup parameter, see the description of the Stash Area (-stsh) startup parameter in the Progress Startup Command and Parameter Reference.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095