1 Reply Latest reply: Dec 6, 2012 12:57 PM by Barbara Boehmer RSS

    CONTAINS - Return only searched words

    899748
      Hi,

      I would like to know if it's possible to use CONTAINS and return only the rows when the result contains ONLY the searched terms.

      I can't find a way to use CONTAINS and "NOT LIKE" together to build a single SELECT statement that works.

      Ex.

      I would like the result to be ROW 1 and2 ONLY.

      SEARCH: "AA CC"

      COL1

      1. AA CC
      2. CC AA
      3. AA BB CC
      4. CC BB AA
      5. AA BB

      Any help is appreciated.
        • 1. Re: CONTAINS - Return only searched words
          Barbara Boehmer
          SCOTT@orcl_11gR2> create table test_tab
            2    (id    number,
            3       col1  varchar2(60))
            4  /
          
          Table created.
          
          SCOTT@orcl_11gR2> insert all
            2  into test_tab values (1, 'AA CC')
            3  into test_tab values (2, 'CC AA')
            4  into test_tab values (3, 'AA BB CC')
            5  into test_tab values (4, 'CC BB AA')
            6  into test_tab values (5, 'AA BB')
            7  select * from dual
            8  /
          
          5 rows created.
          
          SCOTT@orcl_11gR2> create index test_idx
            2  on test_tab (col1)
            3  indextype is ctxsys.context
            4  /
          
          Index created.
          
          SCOTT@orcl_11gR2> variable search varchar2(60)
          SCOTT@orcl_11gR2> exec :search := 'AA CC'
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> select * from test_tab
            2  where  contains (col1, replace (:search, ' ', ' AND ')) > 0
            3  and    length (col1) = length (:search)
            4  /
          
                  ID COL1
          ---------- ------------------------------------------------------------
                   1 AA CC
                   2 CC AA
          
          2 rows selected.