Home » SQL & PL/SQL » SQL & PL/SQL » IN problem (10g,XP)
IN problem [message #421119] Fri, 04 September 2009 06:30 Go to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Hi Guys

Can you explain this behaviour

SQL>  SELECT 'SQL IN trimmed trailing blanks!'  FROM DUAL WHERE 'CA        ' IN ( 'CA', 'US' );

'SQLINTRIMMEDTRAILINGBLANKS!'
-------------------------------
SQL IN trimmed trailing blanks!

Note 'CA '
Re: IN problem [message #421122 is a reply to message #421119] Fri, 04 September 2009 06:35 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
also try giving spaces only by tabs and only by the space bar

I found an answer on google
Blank-Padded and Nonpadded Comparison Semantics

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, NCHAR, text literals, or values returned by the USER function.

[Updated on: Fri, 04 September 2009 06:39]

Report message to a moderator

Re: IN problem [message #421124 is a reply to message #421122] Fri, 04 September 2009 07:05 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
Just to add, you need to remember that if you are using bind variables of varchar2 data type instead of literals then you will see different behavior

XE@SQL> variable x varchar2(10)
XE@SQL> exec :x :='CA'

PL/SQL procedure successfully completed.

XE@SQL> variable y varchar2(10)
XE@SQL> exec :y :='US'

PL/SQL procedure successfully completed.

XE@SQL> SELECT 'SQL IN trimmed trailing blanks!'  FROM DUAL WHERE 'CA        ' IN (:x , :y);

no rows selected

XE@SQL> SELECT 'SQL IN trimmed trailing blanks!'  FROM DUAL WHERE 'CA        ' IN (rpad(:x,10) , rpad(:y,10));

'SQLINTRIMMEDTRAILINGBLANKS!'
-------------------------------
SQL IN trimmed trailing blanks!

1 row selected.



Previous Topic: TX, TM and UL Locks
Next Topic: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Goto Forum:
  


Current Time: Mon Sep 26 17:56:02 CDT 2016

Total time taken to generate the page: 0.06416 seconds