Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aakcse
Aged Yak Warrior
570 Posts |
 Posted - 2009-08-27 : 02:36:45
|
| It is interesting to see the two well known databases Oracle and SQL Server treat blank values so differently.In SQL Server NULL and '' are two different valuesIn Oracle NULL and '' are same.Any comment on this..!!!Regards,CSE |
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2009-08-27 : 05:54:06
|
| One of the senior on Oracle forum commented as below....But has been beaten to death (I think) here over the course of the years in a number of intense debates. However, will not stop me from commenting again on it. A string contains what? Characters. Is the length of a string an attribute of the string, or part of the data type of the string?And that in a nutshell is the difference. The correct view (IMO) is that the length of a string is an attribute. An empty string is the same as a null string. Null means that the variable is without an assigned value. And that is what an empty string is. It does not contain a value - the data type definition say it must contain characters. It does not. Just like a number variable that does not contain a value. Just like date variable that does not contain a value. A null number variable has how many digits? Zero. A null string has how many characters? Zero.So correctly, an empty string is a null string and a null string is an empty string - a variable that does not have an assigned value. Just like variables for the number and date and other data types.The problem is with those that treat a string as a non-scalar data type. But a string data type is not an array data type (especially not in SQL and not in PL/SQL). And that results in the flawed concept of treating an empty string different than a null string - and string variables conceptually different than all other scalar variables.Which is just plain stupid in my view. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-27 : 07:32:20
|
quote:
A null number variable has how many digits? Zero. A null string has how many characters? Zero.
You've hit on an important distinction: null is independent of data type. A null numeric is exactly the same as a null string, but an empty string '' is not a numeric type. What numeric value does '' implicitly convert to? If you say zero, then ''=='0', which is not logically correct. The same issue occurs with all other datatypes and their conversions. And even though all nulls are the same, one null does not equal another.  (it is technically wrong to refer to a null "value")It may be a nice shorthand to treat empty strings as null but they are definitely not the same. If you had to use both Oracle and SQL Server, and if Oracle supports the IS NULL/IS NOT NULL condition, it would be better to use that syntax in both systems. It makes the logic more clear and consistent and avoids their different interpretations of empty strings. |
|
|
|
|
|
|
oracle and sqlserver treat '' values differently