Oracle text : catsearch = 0 behaviour (need to mimic contains = 0)

When i run :

• operator > 0

select * from mytable where CONTAINS(mycolumn, 'HELLO,WORLD',1) > 0; select * from mytable where CATSEARCH(mycolumn, 'HELLO | WORLD',NULL)
> 0;

I get all the lines that contain HELLO or WORLD in mycolumn in both cases. Great.

• operator = 0

select * from mytable where CONTAINS(mycolumn, 'HELLO,WORLD',1) = 0; I get all the lines that do not contain HELLO and do not contain WORLD in mycolumn at the same time.

However when i run this
select * from mytable where CATSEARCH(mycolumn, 'HELLO | WORLD',NULL) = 0;
I get all the lines that contain HELLO or WORLD in mycolumn, it does not work like CONTAINS, it works as if we had put >0.

I've read that catsearch does not compute a score so i understand this behaviour.
But is it still possible to write the query so that the CATSEARCH operator retrieves lines that do not contain all the words specified ?

We need ctxcat indexes because they are automatically updated after DMLs and this is not the case with context indexes.

Thank you in advance

Jean-Michel Received on Tue Jun 07 2011 - 04:00:40 CDT