Oracle SQL "contains" clause tips
Oracle Database Tips by Donald BurlesonConsulting
has extensions for specialized features such as Oracle*Text and
Oracle SQL has a "contains" clause for finding specific strings
within an Oracle*Text index.
Oracle Text works with traditional data columns and also with
XML, MS-Word docs and Adobe PDF files that are stored within Oracle.
Oracle Text has several index types:
WHERE CATSEARCH(text_column, 'ipod')> 0;
CONTEXT Indexes - The CONTEXT index type
is used to index large amounts of text such as Word, PDF, XML,
HTML or plain text documents.
WHERE CONTAINS(test_column, 'ipod', 1) > 0
See my notes here on
using Oracle Text indexes.
Using the CONTAINS clause
documentation for the contains clause notes the basic
Oracle gives this example of invoking the SQL
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');
accumtbl_idx ON accumtbl (text)
indextype is ctxsys.context;
PROMPT dog ACCUM cat
CONTAINS (text, 'dog ACCUM cat', 10) > 0;
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:
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
CONTAINS(text, 'oracle', 1) > 0
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.