Progress
Programming
Handbook
Writing Queries Using the CONTAINS Operator
Once you associate a compiled word-break table with a database that has word indexes, if necessary, populate the database and rebuild the word indexes. You can then write queries that use the CONTAINS operator.
Syntax of the CONTAINS Operator of the WHERE Option
The CONTAINS operator has the following syntax in the WHERE option of the record phrase:
field
A field or array of type CHARACTER that participates in a word index.
string-expression
An expression of type CHARACTER that represents possible contents of field.
The syntax of string–expression is as follows:
word
The word to search for.
The ampersand (&) represents logical AND, while the vertical bar (|), the exclamation point (!), and the caret (^) represent logical OR. AND limits your search to records that contain all words you specify, while OR enlarges your search to include any word you specify. You can combine ANDs and ORs within string–expression . You can also group items with parentheses to create complex search conditions.
You can use a wild card on the end of a string. For example, the string “sales*” represents “sales,” “saleswoman,” “salesman,” “salesperson,” and similar strings.
You can also define a character variable and assign a value to that variable.
Examples of the CONTAINS Operator
Now that you know the syntax of the CONTAINS operator, you can write queries that use it.
The following query, which displays all Item records whose Cat–description field contains the word “hockey,” demonstrates the CONTAINS operator in its simplest form:
The following is the equivalent query in SQL-89, which also allows CONTAINS:
A CONTAINS string can contain multiple words connected by the AND operator (AND or &) and the OR operator (OR, |, or ^), optionally grouped by parentheses. For example:
NOTE: The AND operator takes precedence over the OR operator. To override this default, use parentheses. Using parentheses can also make the text of a query clearer.A CONTAINS string containing multiple contiguous words, such as:
is equivalent to a CONTAINS string containing multiple words connected by AND, such as:
If a CONTAINS string contains multiple words, the order of the words is not significant. To retrieve records in a specific order, use the CONTAINS operator with the MATCHES operator. The following WHERE clause retrieves records whose comments field contains the words “credit” and “hold” in that order, perhaps with other words in between:
Word indexes are case insensitive unless a field participating in the word index is case sensitive. The following two WHERE clauses are equivalent:
You can combine CONTAINS with other search criteria, as in the following WHERE clause, which searches for records whose city field is Boston and whose comments field contains the word “credit” and either the word “hold” or “watch”:
The following example demonstrates the use of a variable with the CONTAINS operator within the WHERE clause:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |