9 Replies Latest reply: Jan 11, 2013 1:46 AM by Roger Ford-Oracle RSS

    Contains: exactly match

    487823
      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
          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
            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
              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
                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
                  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-Oracle
                    "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
                      Rainer Stenzel
                      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
                        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-Oracle
                          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