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 CatDescription field contains the word "hockey," demonstrates the CONTAINS operator in its simplest form:

FOR EACH Item WHERE Item.CatDescription CONTAINS "hockey":
  DISPLAY Item.
END.

The following is the equivalent query in SQL, which also allows CONTAINS:

SELECT * FROM Item
  WHERE CatDescription CONTAINS "hockey".

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:

...CONTAINS "free | gratis | (no & charge)"...
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:

...CONTAINS "credit hold"...

Is equivalent to a CONTAINS string containing multiple words connected by AND, such as:

...CONTAINS "credit AND hold"...

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:

...WHERE Customer.Comments CONTAINS "credit hold"
     AND Customer.Comments MATCHES "*credit*hold*"...

Word indexes are case insensitive unless a field participating in the word index is case sensitive. The following two WHERE clauses are equivalent:

...WHERE Customer.Comments CONTAINS "CREDIT HOLD"...
 
...WHERE Customer.Comments CONTAINS "credit hold"...

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":

...WHERE Custeomer.City = "Boston"
    AND Customer.Comments CONTAINS "credit (hold ^ watch)"...

The following example demonstrates the use of a variable with the CONTAINS operator within the WHERE clause:

DEFINE VARIABLE search_wrd AS CHARACTER NO-UNDO INITIAL "The".

FOR EACH Customer NO-LOCK WHERE Customer.Name CONTAINS search_wrd:
  DISPLAY Customer.CustNum Customer.Name.
END.