Search

Top 60 Oracle Blogs

Recent comments

Literals and VARCHAR2

We all know that comparing two VARCHAR2 fields of different lengths will be treated by Oracle as being not EQUAL:


SQL> drop table T purge;

Table dropped.

SQL> create table T ( x varchar2(10), y varchar2(10));

Table created.

SQL> insert into T values ('abc','abc    ');

1 row created.

SQL> select * from T where x = y;

no rows selected

SQL> select * from T where y = 'abc';

no rows selected

But interestingly enough, if there are no references to tables and/or columns, that is, you are just using plain literals, VARCHAR2 is not the datatype that is in play. For example:


SQL> select * from dual where 'abc' = 'abc  ';

D
-
X

SQL> set serverout on
SQL> exec begin if 'abc' = 'abc  ' then dbms_output.put_line('YES'); end if; end;
YES

PL/SQL procedure successfully completed.



You can see this from the DUMP command, type=1 is VARCHAR2, and type=96 is CHAR.


SQL> select dump(x) from t;

DUMP(X)
-------------------------------
Typ=1 Len=3: 97,98,99


SQL> select dump('abc') from dual;
 
DUMP('ABC')
----------------------
Typ=96 Len=3: 97,98,99