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.
12 responses
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.