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

jimi_xyz_at_hotmail.com wrote:
> 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

Like in Morgan's Library at psoug.org
Scroll down to Wildcards
See if the solution works for you.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)