a) As a Result:
select length(null) from dual; -- NULL as expected
select length('') from dual; -- NULL too, what was a bit unexpected
select length(' ') from dual; -- 1 as expected
b) In a Comparison:
select dummy from dual where '' = ''; -- Instead of 'X' no result, comparison failed
select dummy from dual where '' = NULL; -- Same as above, not a real surprise
select dummy from dual where '' IS NULL; -- The expected result 'X'
Searching at "Ask Tom" I found the following statement:
and we said...
A zero length varchar is treated as NULL.
'' is not treated as NULL.
'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).
'' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no long '')
In the Oracle SQL Language Reference I found in addition the following comment regarding the difference of VARCHAR and VARCHAR2:
VARCHAR Datatype
Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.
So what does that all mean from a practical point of view:
- Oracle does not handle null values for VARCHAR ANSI SQL 92 conform as the empty string is converted to a null value.
- Always use VARCHAR2 out of compatibility reasons as for VARCHAR Oracle has reserved the right to change the comparison semantics at a later stage to become ANSI SQL 92 compatible.
- For all CHAR types due to padding for '' everything is padded with spaces.
- The behaviour of empty or zerlo length Strings described is the same in all Oracle releases.
This behaviour could become important when we think about query design for Database Optimization and Tuning. Enjoy & experiment on your own!
