Oracle8i Application Developer's Guide - XML
Release 3 (8.1.7)

Part Number A86030-01


Solution Area



Go to previous page Go to beginning of chapter Go to next page

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"


Here is the CONTAINS syntax:

         text_query       VARCHAR2,
         [label            NUMBER])


Table 5-1 CONTAINS Operator: Syntax Description
Syntax  Description 


Specify the text column to be searched on. This column must have a Text index associated with it.  


Specify the query expression that defines your search in column.  


Optionally specify the label that identifies the score generated by the CONTAINS operator.  


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
   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.


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;

Go to previous page Go to beginning of chapter Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.


Solution Area