HOW TO: Un-escape a wildcard in a escape sequence

Hi,
I was hoping someone could lead to the correct path, for this question. I am using Oracle 9i and indexed a couple columns with ctxsys.context. The users will be searching terms that contain, a bunch of Reverse Words and there Characters. So when the query gets to the API, it will look like this..

select count(projectid) from project
where contains(objectives, 'agent-base', 1) >0;

This cause an incorrect result because of the "-" sign, there are other examples, but basically what i want to be able to do is, enclose the users search string in the escape charecters "{}", for example..

select count(projectid) from project
where contains(objectives, '{agent-base}', 1) > 0;

So now any character Oracle treats as a Reverse word, it will instead search for that character. My question is what if i want to use a wildcard, "%", in my query. For example...

select count(projectid) from project
where contains(objectives, '{agent-base%}', 1) > 0;

This doesn't treat the "%" as a wildcard, instead its going to search for the "%". So my question is how do I Un-escape a wildcard in the escape sequence..

How do I make this query work?

select count(projectid) from project
where contains(objectives, '{agent-base%}', 1) > 0;

NOTE: I want the percent sign to work as a wildcard.

Please any idea will be greatly appreciated, Jimi Received on Tue Sep 20 2005 - 12:11:40 CDT