2

I am trying to compose a text search query which would return records containing a certain value in one section and NOT containing a certain value in another section.

Something like that:

SELECT * FROM text_search_table WHERE (contains(text_search, 
'(value1 within section1) AND (NOT(value2) within section2)') > 0)

The syntax of this query is incorrect since NOT operator requires two operands. I also tried using % as the first operand:

% NOT value2 within section2

It doesn't work either.

Is there a way in text search to achieve what I need?

Thank you.

Emilio Cervantez
  • 43
  • 1
  • 5

1 Answers1

1

I'm fairly certain (not 100%) that you can't use the NOT operator with the WITHIN in the manner you want to. The standard way might be to do the following instead:

select * 
  from text_search_table 
 where contains(text_search, 'value1 within section1') > 0
   and not contains(text_search, 'value2 within section2') > 0

However, you can cheat this slightly. If you know that some text appears in the second section you can utilise the NOT operator to search for that and NOT the thing you're looking for:

select * 
  from text_search_table 
 where contains(text_search
     , 'value1 within section1 and (known_value not value2) within section2') > 0

As the known value exists it's always within the section. If value2 is within the section then nothing is returned; if it it's not within the section then the condition is false and you retrieve it.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • Yes, it seems the first option is the only way. The second option is not suitable for me, since I know only the value which must NOT be in the text search section. That's why I tried `% NOT value2 within section2` thinking that may be Oracle would see it as 'anything but NOT value2 within section2' – Emilio Cervantez Sep 09 '13 at 13:33