2 Replies Latest reply: Nov 5, 2012 7:54 AM by 721850 RSS

    Make Contains not match the same word twice

    721850
      I'm guessing this won't work, but I'm throwing it out there anyway.

      I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.

      The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:
      Select * From AddressSearch
      Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;
      But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?
      Thanks!
        • 1. Re: Make Contains not match the same word twice
          Roger Ford-Oracle
          You can do
          select * from addressSearch where contains (address, 'Stockholm AND (Stock% MINUS Stockholm)') > 0;
          Here's my testcase:
          drop table addressSearch;
          
          create table addressSearch (address varchar2(2000));
          
          insert into addressSearch values ('Stockholm, Stockrosvägen');
          insert into addressSearch values ('Stockholm, Stockbergsvägen');
          insert into addressSearch values ('Stockholm, Othervägen');
          insert into addressSearch values ('London, Stockstreet');
          
          -- try some repeated words to make sure it still works correctly
          insert into addressSearch values ('Stockholm Stockholm, Stockrosvägen');
          insert into addressSearch values ('Stockholm, Stockrosvägen Stockrosvägen');
          
          create index addressIndex on addressSearch(address) 
          indextype is ctxsys.context
          /
          
          select * from addressSearch where contains (address, 'Stockholm AND (Stock% MINUS Stockholm)') > 0;
          • 2. Re: Make Contains not match the same word twice
            721850
            Yes, that worked! Thanks a bunch!