Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

Using interMedia Text to Search and Retrieve Data from XML Documents, 6 of 22

## Querying with the CONTAINS Operator

InterMedia Text's main purpose is to provide an implementation for the CONTAINS operator. The CONTAINS operator is used in the WHERE clause of a SELECT statement to specify the query expression for a Text query.

See Also: "Building Query Applications with interMedia Text".

#### CONTAINS Syntax

Here is the CONTAINS syntax:

    CONTAINS(
             [schema.]column,
             text_query       VARCHAR2,
             [label            NUMBER])
    RETURN NUMBER;

where:

##### Table 5-1 CONTAINS Operator: Syntax Description

Syntax | Description
--- | ---
[schema.]column | Specify the text column to be searched on. This column must have a Text index associated with it.
text_query | Specify the query expression that defines your search in column.
label | Optionally specify the label that identifies the score generated by the CONTAINS operator.
Returns | For each row selected, CONTAINS returns a number between 0 and 100 that indicates how relevant the document row is to the query. The number 0 means that Oracle found no matches in the row. You can obtain this score with the SCORE operator. Note: You must use the SCORE operator with a label to obtain this number.

### CONTAINS Example 1

The following example illustrates how the CONTAINS operator is used in a SELECT statement:

    SELECT id FROM my_table
       WHERE
       CONTAINS (my_column, 'receipts') > 0

The 'receipts' parameter of the CONTAINS function is called the "Text Query Expression". See "Text Query Expression" for an example of how to use this.

Note: The SQL statement with the CONTAINS function requires a text index in order to run.

### CONTAINS Example 2: Using Score Operator with a Label

The following example searches for all documents in the text column that contain the word Oracle. The score for each row is selected with the SCORE operator using a label of 1:

    SELECT SCORE(1), title from newsindex
               WHERE CONTAINS(text, 'oracle', 1) > 0;

The CONTAINS operator must always be followed by the > 0 syntax which specifies that the score value calculated by the CONTAINS operator must be greater than zero for the row to be selected.

### CONTAINS Example 3: Using the SCORE Operator

When the SCORE operator is called, such as in a SELECT clause, the operator must reference the label value as in the following example.

    SELECT SCORE(1), title from newsindex
               WHERE CONTAINS(text, 'oracle', 1) > 0 ORDER BY SCORE(1) DESC;

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index