SQL escape games
- From: Martin Klier <usn@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 30 Aug 2010 18:20:06 +0200
Hi listers,
I've got a weird issue with escaping in a SELECT's where condition (like
pattern). The error first occurred in a JDBC client, and PL/SQL
developer shows the "wrong result" as well. Oracle SQL*Developer
displays as expected.
In SQL*Plus I can reproduce both cases:
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);
COUNT(*)
----------
6
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);
COUNT(*)
----------
14
Can anybody explain this behaviour? In the "wrong" case, the '/' ist
definitely recognized as not being part of the string, because if I
remove it from the LIKE statement and just keep the ESCAPE clause in
place, i get count=0 as expected by looking at the data (no slash in the
field)
Thanks a lot in advance
Martin
--
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
