CONTAINS

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.

The grammar for this operator is called CONTEXT. You can also use CTXCAT grammar if your application works better with simpler syntax. To do so, use the Query Template Specification in the text_query parameter as described in this section.

Syntax

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

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

Specify one of the following:

• the query expression that defines your search in column.

• a marked-up document that specifies a query template. You can use one of the following templates:

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 be followed by an expression such as > 0, which specifies that the score value calculated must be greater than zero for the row to be selected.

When the SCORE operator is called (for example, in a SELECT clause), the CONTAINS clause must reference the score label value as in the following example:

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

The following example specifies that the query be parsed using the CATSEARCH grammar:

SELECT id FROM test WHERE CONTAINS (text,
 '
   
      cheap pokemon
   
   
  ' ) > 0;

CATSEARCH
Use the CATSEARCH operator to search CTXCAT indexes. Use this operator in the WHERE clause of a SELECT statement.
The grammar of this operator is called CTXCAT. You can also use the CONTEXT grammar if your search criteria requires special functionality, such as thesaurus, fuzzy matching, proximity searching or stemming. To utilize the CONTEXT grammar, use the Query Template Specification in the text_query parameter as described in this section.
About Performance
You use the CATSEARCH operator with a CTXCAT index mainly to improve mixed query performance. You specify your text query condition with text_query and your structured condition with structured_query.
Internally, Oracle Text uses a combined b-tree index on text and structured columns to quickly produce results satisfying the query.
Limitation
If the optimizer chooses to use the functional query invocation, your query will fail. The optimizer might choose functional invocation when your structured clause is highly selective.
Syntax
CATSEARCH(
[schema.]column,
text_query       VARCHAR2,
structured_query VARCHAR2,
RETURN NUMBER;
[schema.]column
Specify the text column to be searched on. This column must have a CTXCAT index associated with it.
text_query
Specify one of the following to define your search in column.
CATSEARCH query operations
Query Template Specification (for using CONTEXT grammar)
CATSEARCH query operations
The CATSEARCH operator supports only the following query operations:
Logical AND
Logical OR (|)
Logical NOT (-)
" " (quoted phrases)
Wildcarding
These operators have the following syntax:
Table 1-2 CATSEARCH Query Operators
Operation
Syntax
Description of Operation
Logical AND
a b c
Returns rows that contain a, b and c.
Logical OR
a | b | c
Returns rows that contain a, b, or c.
Logical NOT
a - b
Returns rows that contain a and not b.
hyphen with no space
a-b
Hyphen treated as a regular character. 
For example, if the hyphen is defined as skipjoin, words such as web-site are treated as the single query term website.
Likewise, if the hyphen is defined as a printjoin, words such as web-site are treated as web-site in the CTXCAT query language.
" "
"a b c"
Returns rows that contain the phrase "a b c". 
For example, entering "Sony CD Player" means return all rows that contain this sequence of words.
( )
(A B) | C
Parentheses group operations. This query is equivalent to the CONTAINS query (A &B) | C.
wildcard 
(right and double truncated)
term* 
a*b
The wildcard character matches zero or more characters. 
For example, do* matches dog, and gl*s matches glass.
Left truncation not supported.
Note: Oracle recommends that you create a prefix index if your application uses wildcard searching. You set prefix indexing with the BASIC_WORDLIST preference.

 
The following limitations apply to these operators:
The left-hand side (the column name) must be a column named in at least one of the indexes of the index set.
The left-hand side must be a plain column name. Functions and expressions are not allowed.
The right-hand side must be composed of literal values. Functions, expressions, other columns, and subselects are not allowed.
Multiple criteria can be combined with AND. OR is not supported.
For example, these expressions are supported:
catsearch(text, 'dog', 'foo > 15')
catsearch(text, 'dog', 'bar = ''SMITH''')
catsearch(text, 'dog', 'foo between 1 and 15')
catsearch(text, 'dog', 'foo = 1 and abc = 123')

And these expression are not supported:
catsearch(text, 'dog', 'upper(bar) = ''A''')
catsearch(text, 'dog', 'bar LIKE ''A%''')
catsearch(text, 'dog', 'foo = abc')
catsearch(text, 'dog', 'foo = 1 or abc = 3')
Query Template Specification
You specify a marked-up string that specifies a query template. You can specify one of the following templates:
query rewrite, used to expand a query string into different versions
progressive relaxation, used to progressively issue less restrictive versions of a query to increase recall
alternate grammar, used to specify CONTAINS operators (See CONTEXT Query Grammar Examples)
alternate language, used to specify alternate query language
alternate scoring, used to specify alternate scoring algorithms

See Also:
The text_query parameter description for CONTAINS for more information about the syntax for these query templates.

structured_query
Specify the structured conditions and the ORDER BY clause. There must exist an index for any column you specify. For example, if you specify 'category_id=1 order by bid_close', you must have an index for 'category_id, bid_close' as specified with CTX_DDL.ADD_INDEX.
With structured_query, you can use standard SQL syntax with only the following operators:
=
<=
>=
>
<
IN
BETWEEN
AND (to combine two or more clauses)

Note:
You cannot use parentheses () in the structured_query parameter.

Examples
Create the Table
The following statement creates the table to be indexed.
CREATE TABLE auction (category_id number primary key, title varchar2(20), 
bid_close date);

The following table inserts the values into the table:
INSERT INTO auction values(1, 'Sony CD Player', '20-FEB-2000');
INSERT INTO auction values(2, 'Sony CD Player', '24-FEB-2000');
INSERT INTO auction values(3, 'Pioneer DVD Player', '25-FEB-2000');
INSERT INTO auction values(4, 'Sony CD Player', '25-FEB-2000');
INSERT INTO auction values(5, 'Bose Speaker', '22-FEB-2000');
INSERT INTO auction values(6, 'Tascam CD Burner', '25-FEB-2000');
INSERT INTO auction values(7, 'Nikon digital camera', '22-FEB-2000');
INSERT INTO auction values(8, 'Canon digital camera', '26-FEB-2000');

Create the CTXCAT Index
The following statements create the CTXCAT index:
begin
ctx_ddl.create_index_set('auction_iset');
ctx_ddl.add_index('auction_iset','bid_close'); 
end;
/
CREATE INDEX auction_titlex ON auction(title) INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS ('index set auction_iset');

Query the Table
A typical query with CATSEARCH might include a structured clause as follows to find all rows that contain the word camera ordered by bid_close:
SELECT * FROM auction WHERE CATSEARCH(title, 'camera', 'order by bid_close desc')>
0;

CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
          8 Canon digital camera 26-FEB-00
          7 Nikon digital camera 22-FEB-00

The following query finds all rows that contain the phrase Sony CD Player and that have a bid close date of February 20, 2000:
SELECT * FROM auction WHERE CATSEARCH(title, '"Sony CD Player"',
'bid_close=''20-FEB-00''')> 0;

CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
          1 Sony CD Player       20-FEB-00

The following query finds all rows with the terms Sony and CD and Player:
SELECT * FROM auction WHERE CATSEARCH(title, 'Sony CD Player', 'order by bid_close
desc')> 0;
CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
          4 Sony CD Player       25-FEB-00
          2 Sony CD Player       24-FEB-00
          1 Sony CD Player       20-FEB-00

The following query finds all rows with the term CD and not Player:
SELECT * FROM auction WHERE CATSEARCH(title, 'CD - Player', 'order by bid_close
desc')> 0;

CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
          6 Tascam CD Burner     25-FEB-00

The following query finds all rows with the terms CD or DVD or Speaker:
SELECT * FROM auction WHERE CATSEARCH(title, 'CD | DVD | Speaker', 'order by
bid_close desc')> 0;

CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
          3 Pioneer DVD Player   25-FEB-00
          4 Sony CD Player       25-FEB-00
          6 Tascam CD Burner     25-FEB-00
          2 Sony CD Player       24-FEB-00
          5 Bose Speaker         22-FEB-00
          1 Sony CD Player       20-FEB-00

The following query finds all rows that are about audio equipment:
SELECT * FROM auction WHERE CATSEARCH(title, 'ABOUT(audio equipment)', NULL)> 0;