Oracle SQL "contains" clause tips Oracle Tips by Burleson Consulting

WHERE CATSEARCH(text_column, 'ipod')> 0;

   WHERE CONTAINS(test_column, 'ipod', 1) > 0

See my notes here on using Oracle Text indexes.
 

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

Oracle gives this example of invoking the SQL contains clause:

CREATE TABLE accumtbl
   (id NUMBER, text VARCHAR2(4000) );
 
INSERT INTO accumtbl VALUES
   ( 1, 'the little dog played with the big dog
      while the other dog ate the dog food');
INSERT INTO accumtbl values
   (2, 'the cat played with the dog');
 
CREATE INDEX
   accumtbl_idx ON accumtbl (text)
   indextype is ctxsys.context;
 
PROMPT dog ACCUM cat

SELECT
   SCORE(10)
FROM
   accumtbl
WHERE
   CONTAINS (text, 'dog ACCUM cat', 10) > 0;
 

The Oracle Text SQL Operator documentation notes:

"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;

"

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.