This discussion is archived
9 Replies Latest reply: Jan 10, 2013 11:46 PM by Roger Ford RSS

Contains: exactly match

487823 Newbie
Currently Being Moderated
Hi, all!
Can you tell me how can i command contains predicate to search exactly? For example, if i have in my text strings 123456 and 1234 and when i use
... contains(txt, '1234', 1) ...
the result has to rows, but i need the one.
How shuold i reach need that goal?
Thanks
  • 1. Re: Contains: exactly match
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    I am not seeing a problem. It works as you desire, as demonstrated below. You must have some additional settings that are inappropriate for what you want.
    scott@ORA92> CREATE TABLE your_table
      2    (txt VARCHAR2(30))
      3  /
    
    Table created.
    
    scott@ORA92> INSERT ALL
      2  INTO your_table VALUES ('123456')
      3  INTO your_table VALUES ('1234')
      4  SELECT * FROM DUAL
      5  /
    
    2 rows created.
    
    scott@ORA92> CREATE INDEX your_index
      2  ON your_table (txt)
      3  INDEXTYPE IS CTXSYS.CONTEXT
      4  /
    
    Index created.
    
    scott@ORA92> SELECT token_text FROM dr$your_index$i
      2  /
    
    TOKEN_TEXT
    ----------------------------------------------------------------
    1234
    123456
    
    scott@ORA92> SELECT * FROM your_table
      2  WHERE contains(txt, '1234', 1) > 0
      3  /
    
    TXT
    ------------------------------
    1234
    
    scott@ORA92> 
  • 2. Re: Contains: exactly match
    tcline Newbie
    Currently Being Moderated
    I seem to have this problem too...

    insert into your_table values ('hawaii');
    insert into your_table values ('playa hawaii');

    Commit;

    begin
    ctx_ddl.sync_index('your_index');
    end;
    /

    select score(1), txt from your_table where contains(txt, '{hawaii}', 1) > 0;

    score(1) TXT
    ------------ ----------------------------
    4 HAWAII
    4 PLAYA HAWAII

    So how does one perform a true exact search this way without using minus or other operators?

    Thanks!
  • 3. Re: Contains: exactly match
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    This is not what Oracle Text is intended for. Your query asked for all rows that contain the word "hawaii" and that is what you got. If you want an exact match, then don't use Oracle Text, just:

    select txt from your_table where txt = 'hawaii';
  • 4. Re: Contains: exactly match
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    If you want to combine results of a search for an exact match with Oracle Text results, you could apply a 100 score to the exact match and union all the results together. Alternatively, you could use utl_match.edit_distance or utl_match.edit_distance_similarity to compare strings. There are some examples in this forum.
  • 5. Re: Contains: exactly match
    tpmuhammedaslam Newbie
    Currently Being Moderated
    Hi,

    How to Display the exact match in the top. I need all the matches and display the exact match in the top (first row).

    Thanks,
    Muhammed Aslam
  • 6. Re: Contains: exactly match
    Roger Ford Expert
    Currently Being Moderated
    "Exact match" means different things to different people.

    Let's assume you have data:

    1. smith
    2. Smith
    3. John Smith
    4. Smithson
    5. Pete Smithson
    6. Hammersmith
    7. Johns Mith

    If your user searches for "smith" which of these do you want to return (note that 7 is hard and will limit the size of string you can deal with) and in what order?
  • 7. Re: Contains: exactly match
    499773 Newbie
    Currently Being Moderated
    If using context indexes for performance reasons on table character columns you could add a LIKE predicate to restrain the result set.

    ... contains(txt, '1234', 1) and txt like '%1234%' ...
  • 8. Re: Contains: exactly match
    tpmuhammedaslam Newbie
    Currently Being Moderated
    In my case, smith should be the first row of the result. Apart from this ,search should return other results except 7th one Johns Mith .
    Currently I am getting the expected result in 3rd position.
  • 9. Re: Contains: exactly match
    Roger Ford Expert
    Currently Being Moderated
    Does "smith" have to come ahead of "Smith" can they both be ranked equally?

    Oracle Text doesn't directly allow you to specify the word position, you can't say "I want Smith as the first word" or "I want Smith as the only word". To do that you have to add markers for the start and end of the text, and do a phrase search.

    You can do this using the MULTI_COLUMN_DATASTORE to add fixed strings either side of the text - I've used "xxstart" and "xxend".

    Then you can use the PROGRESSIVE RELAXATION feature to decide which you want to appear first in the result set. In the example below I've chosen to sort them by:

    1/ Only the complete search term on its own
    2/ Any single word containing the search term
    3/ The complete search term anywhere in the text
    4/ Any word containing the search term anywhere in the text
    drop table names;
    create table names (id number primary key, text varchar2(50));
    
    insert into names values( 1, 'smith' );
    insert into names values( 2, 'Smith' );
    insert into names values( 3, 'John Smith' );
    insert into names values( 4, 'Smithson' );
    insert into names values( 5, 'Pete Smithson' );
    insert into names values( 6, 'Hammersmith' );
    insert into names values( 7, 'Johns Mith' );
    
    exec ctx_ddl.drop_preference  ( 'mcds' )
    exec ctx_ddl.create_preference( 'mcds',  'MULTI_COLUMN_DATASTORE' )
    exec ctx_ddl.set_attribute    ( 'mcds', 'COLUMNS', '''xxstart ''||text||'' xxend''' )
    
    exec ctx_ddl.drop_preference  ( 'mywl' )
    exec ctx_ddl.create_preference( 'mywl', 'BASIC_WORDLIST' )
    exec ctx_ddl.set_attribute    ( 'mywl', 'SUBSTRING_INDEX', 'YES' )
    
    create index namesindex on names(text)
    indextype is ctxsys.context
    parameters( 'datastore mcds wordlist mywl' )
    /
    
    select * from names where contains( text, '
    <query>
      <textquery>
        <progression>
          <seq> xxstart smith xxend </seq>
          <seq> xxstart %smith% xxend </seq>
          <seq> smith </seq>
          <seq> %smith% </seq>
        </progression>
      </textquery>
    </query>
    ', 1) > 0 
    order by score(1) desc
    /
    The output of this is:
         ID TEXT
    ---------- --------------------------------------------------
          1 smith
          2 Smith
          4 Smithson
          6 Hammersmith
          3 John Smith
          5 Pete Smithson