In Oracle 8, there is no such thing as a zero-length string. Any zero-length string, either from a function call or the literal '', is treated as null.
Some of the consequences of this are counterintuitive. Here are some examples.
- The expression
X=''is never true, regardless of whatXis. In particular: - The expression
''=''is not true. - If you store an empty string (
'') and read it back, the result will be null. - The expression
trim(' ')returns null.
Be careful of this when writing your queries, particularly if you are porting code between Oracle and another database.
It’s a really bad behavior ๐ In MySQL empty string is not equal to null.
That really should be considered as a Oracle design bug. Oracle really is not that good in my opinion.
I am not at all an Oracle expert, but as a developer I have found Oracle databases to be annoying to work with. I think their selling point is the boring enterprise stuff like reliability, scalability, etc.
I personally like the fact that Oracle treats empty strings as null. Its a real pain having to test if something is null OR an empty string to find out if there is a usuable value there or not. And if I want to select rows where certain fields have blank values, I know that I will get everything I need by testing if null.
Programming without null is like Mathematics without zero.
I agree, ORACLE storing empty strings as NULL was a horrible design blunder. In a relational database, NULL is used to represent datum whose value is “unknown”. If I insert an empty string into a database field, I have specified a value for that field — a zero-length string. If I fetch that string from the database, the only correct result is an empty string.
Oops, correction:
A string variable that can’t be set empty is like a number variable that can’t be set zero.
bachew – I agree.
Also, you came back 20 days later to fix that. Awesome.
@Michael: if you don’t want to test for both ” and null, and your application treats both as semantically the same, just declare the field ‘not null’
I agree that this is a horrible design decision. Not just because it screws compatibility with most other databases but because there is a school of thought held by many (the likes of C.J Date, and Hugh Darwin among them) that believe NULL is a Bad Idea and that there are better ways of representing UNKNOWN and NOT APPLICABLE (both of which people tend to use NULL for, often indistinguishably). This makes it almost impossible to excecise this discipline in Oracle.
As to the comment “Programming without null is like Mathematics without zero.” this is completely wrong-headed. A numeric 0 in a relational database is a 0 not a null. Tell me, what is x + y if x or y is NULL?
NULL complicates EVERYTHING and turns the clean 2 value logic of relational theory into the much more complex 3 value logic and this is the source of many problems.
Heres my two sense. Porting from SQL Server to Oracle (don’t ask why). In Sql server had:
select field1,field2 from table where field1 ”
This returned no results in Oracle, even though the majority of those fields had values. There was one that was null, which killed the query.
In Oracle I tried tiering it with
select field1,field2
from table
where trim(field1) ” and field1 is not null
which also died, no results. The solution was
select field1,field2
from table
where trim(field1) is not null
In my opinion, that is just poor db design. The majority of the rows had values, but oracle died on the one null. A large relational database should be able to handle a null value the same way it handles a regular value, but nulls always blow up on oracle.
that i need
@Bennett: if you call reliability and scalability boring enterprise stuff I guess you never had valuable data on a db ๐
@Robert: NULLs don’t blow up on Oracle, it didn’t die on one NULL, it just considers ” as NULL, and an equality operator on a NULL is always false, it’s like saying WHERE col = NULL. That will always be false.
You can also try WHERE ” = ” OR NULL = NULL, you won’t have any results.
Anyway… I can’t think of a proper use of ” instead of NULL. I know it’s non standard but can you supply an example where this differentiation would be useful?
I guess most of its use comes from poorly sanitized input.
Cheers
@Kamal, I do think reliability and scalability are important. But they’re also boring to me. I’m glad that some people are interested in them though.
One of the more common uses of null is to indicate “unknown” as @Eric mentioned.
label='large'means that the label is “large”,label=''means the label is blank, andlabel=nullmeans that we don’t know. In Oracle you have to do this with an extra Boolean field.And let’s not forget that in Oracle,
I would love to see anybody try to argue that this isn’t nonsense.
I just got bitten in the rear by this. Trying to find records with blank (whitespace) non-null values in some fields, I thought of comparing the length of the field vs the length of the trimmed field:
WHERE length(field) > 0 AND length(trim(field)) 0 AND trim(field) is null
…which doesn’t make a whole lot of sense outside of Oracle’s little world of weird logic, but works.
So thanks, Bennett, for explaining, and thanks, Oracle, for nothing ๐
I can’t beleive I’m here again…
I came across this problem 8 (yes, that’s right EIGHT) years ago when I first tried to port our software to Oracle (I think at the time it was 8i or something).
I’m now trying to port again, this time to 11g and its STILL a problem.
Now after all this time I was sure that Oracle would have sorted it – BUT NO – still the same problem. Trying to insert an empty string into a NOT NULL column results in “ORA-01400: cannot insert NULL” AAAArrrrrhhhhhh!!!! PLEASE, PLEASE, PLEASE, NO, NO, NO.
One word – crap.
Kamal,
Imagine a column containing phone prefixes, where ‘1’ means North America, ‘1212’ means Manhattan Area 212, and ” means … (did you guess it?) the whole world. You may still want to have null in this column in case the prefix is (still) unknown, but that’s not important. We need to use this value in order, length, compare and other expression as a valid and meaningfull value, which is certanly different from null ะฐnะด it behaviour.