Re: Escaping an underscore wildcard?

In article <F451DI.8vy_at_world.std.com>,
  enquad_at_world.std.com (MWRA ENQUAD) wrote:
> I have production tables and work tables in the same tablespace.
> Each worktable is given a letter prefix followed by an underscore
> to distinguish it from the production tables, and to allow all
> worktables with a common prefix to be identified. We use Oracle v7.1.
>
> I would like to be able to do the following:
> Select tname from tab where tname like 'A_%';
>
> The problem is that the underscore is a single character
> wildcard. So in addition to returning tablenames like
> A_APPLE, I also get APPLE.
>
> I thought that I might be able to use the backslash as an escape like
> this:
> Set escape on
> Select tname from tab where tname like 'A\_%';
> but the backslash is ignored and the underscore is still used as a
> wildcard. It seems that the escape only works to escape variable
> substitution not wildcard substitution.
>
> I would love to be able to temporarily change the wildcard character
> to something other than and underscore, but this doesn't seem
> possible.
>
> Any suggestions. Thanks.
>
> --
>
>

Looks like you're right. However, try this as a workaround. The optimizer should still use the index for the LIKE and apply the SUBSTR() afterward.

   Select tname from tab
   where tname like 'A_%'
     and substr(tname,1,2)='A_' ;

Underscore is not a wildcard except in the LIKE clause.

Enjoy!

   ed

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Dec 18 1998 - 11:49:43 CST