Literals and VARCHAR2

January 21, 2016

1–2 minutes

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 responses to “Literals and VARCHAR2”

Anton

January 21, 2016 at 5:01 pm

To quote from the documentation, https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF51037, “Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.”

Reply
Kevan Gelling

January 21, 2016 at 10:28 pm

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.

Reply
Nimish Garg (@nimishgarg)

January 22, 2016 at 4:10 pm

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

Reply