This discussion is archived
2 Replies Latest reply: Nov 5, 2012 5:54 AM by 721850 RSS

Make Contains not match the same word twice

721850 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Yes, that worked! Thanks a bunch!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points