Re: SQL escape games
- From: Jared Still <jkstill@xxxxxxxxx>
- To: usn@xxxxxxxxx
- Date: Mon, 30 Aug 2010 10:25:33 -0700
Comments inline:
On Mon, Aug 30, 2010 at 9:20 AM, Martin Klier <usn@xxxxxxxxx> 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
Other related posts:
