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

3 thoughts on “Literals and VARCHAR2

  1. That sounds all very clear, however, I still get some odd behaviour.

    On 12.1.0.2, equal but not equal:

    SQL> select case when ‘abc’ = ‘abc ‘ then ‘Y’ else ‘N’ end equal
    from dual where ‘abc’ = ‘abc ‘ ;

    E

    N

    1 row selected

    On 11.2.0.4, equal but maybe not:

    SQL> select case when x = y then ‘Y’ else ‘N’ end equal
    from ( select ‘abc’ x, ‘abc ‘ y from dual )

    E

    Y

    1 row selected.

    SQL> select case when x = y then ‘Y’ else ‘N’ end equal
    , case when substr(‘abc’,1) || x = y then ‘y’ else ‘N’ end not_anymore
    from ( select ‘abc’ x, ‘abc ‘ y from dual )

    E N
    – –
    N N

    1 row selected.

  2. dump seems to be see the difference between literals

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

    SQL> select dump(‘abc ‘) from dual;
    DUMP(‘ABC’)
    —————————-
    Typ=96 Len=5: 97,98,99,32,32

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s