Menu
I have this table where NULL is the NULL value, not the string NULL:
MYCOL
--------
NULL
example
Why does this query not return the NULL row?
select * from example_so where nvl(mycol, '') = '';
920
The value null represents the absence of any object, while the empty string is an object of type String with zero characters. If you try to compare the two, they are not the same.
NVL replaces a null with a string. NVL returns the replacement string when the base expression is null, and the value of the base expression when it is not null. To replace an expression with one value if it is null and a different value if it is not, use NVL2 .
NVL. The NVL function allows you to replace null values with a default value. If the value in the first parameter is null, the function returns the value in the second parameter. If the first parameter is any value other than null, it is returned unchanged.
In database terms, however, a null value is a value that doesn't exist: the field does not contain a value of any kind (not even a blank value). By contrast, a blank value is a real value: it just happens to be a string value containing 0 characters.
'' is again NULL in Oracle, because Oracle doesn't support empty Strings just like Other High Level languages or DBMS..
You need to look for NULL/empty string using IS NULL or IS NOT NULL
No other relational operator work against NULL, though it is syntactically valid. SQLFiddle Demo
It has to be,
select * from example_so where mycol IS NULL
EDIT: As per Docs
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
