Re: SQL escape games

On Mon, Aug 30, 2010 at 9:20 AM, Martin Klier <usn_at_usn-it.de> wrote:

> Correct result:
>
> SQL> set escape '/'
> SQL> Select count(*) From table Where Mailordernocustomerupper Like
> '%GF/_2%' And Warehousesiteid In (4) And Status<99 And Clientid In (6);
>
>

So this one is finding a literal '/' in the data. eg <any number of any characters>GF/<any single character>2<any number of any characters>

> Wrong result:
>
> SQL> set escape off
> SQL> Select count(*) From table Where Mailordernocustomerupper Like
> '%GF/_2%' escape '/' And Warehousesiteid In (4) And Status<99 And
> Clientid In (6);
>
>

... and this one is doing something slightly different it is finding the Pattern 'GF_2' in the middle of a string. Normally the '_' character is a single character wildcard for LIKE, but this SQL has identified the '_' as a literal character, not to be used as a wildcard.

eg <any number of any characters>GF_2<any number of any characters>

The 'set escape' command sets the escape character to be used in sqlplus, and has no effect on SQL.

From the sqlplus docs:

> You can use the escape character before the substitution character (set
> through SET DEFINE) to indicate that SQL*Plus should treat the substitution
> character as an ordinary character rather than as a request for variable
> substitution.

The SQL must contain the 'escape' clause along with the escape character.

HTH Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

--
http://www.freelists.org/webpage/oracle-l