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;