CONTAINS

Purpose

Use the CONTAINS operator in the WHERE clause of a SELECT statement to specify the query expression for a Text query.

CONTAINS returns a relevance score for every row selected. You obtain this score with the SCORE operator.

Syntax

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

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

See Also: For more information about the Text operators you can use in query expressions, see Chapter 4, "Query Operators".

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.

Example

The following example searches for all documents in the 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.

When the SCORE operator is called (e.g. 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;

Notes

Querying Multi-Language Tables

With the multi-lexer preference, you can create indexes from multi-language tables.

At query time, the multi-lexer examines the session's language setting and uses the sub-lexer preference for that language to parse the query. If the language setting is not mapped, then the default lexer is used.

When the language setting is mapped, the query is parsed and run as usual. Since the index contains tokens from multiple languages, such a query can return documents in several languages. To limit you query to a given language, use a structured clause on the language column.

Related Topics

SCORE

Appendix A, "Query Tuning"