Oracle empty string == NULL behavior in SQLServer 2k5?

I'd like to have Oracle's empty string behavior in SQLServer 2k5. Oracle treats an empty string as NULL's.

In PL/SQL can do:
SELECT * FROM TABLE WHERE TABLE.FIELD IS NULL
... and it'd return rows containing NULL's as well as empty strings.

Can this be done? I couldn't find a setting for it.

Thanx

PeterAs far as I know, the only way to do this isSELECT * FROM theTable WHERE field IS NULL or field = ''|||OrSELECT * FROM theTable WHERE ISNULL(field, '') = ''|||As far as I know, the only way to do this isSELECT * FROM theTable WHERE field IS NULL or field = ''

Yep, thanx but we got this far. We were hoping to avoid typing this. Maybe some system wide setting... but I guess we'll have to learn to do it the MS way. :)|||It is less doing things the MS way and more not doing things the Oracle way. I didn't know you could do that in PL\SQL but it sure as 'ecky thump isn't part of the ANSI standard.

Anyway - a lot of people just prevent nulls from their database in the first place.


CREATE TABLE MyTable
(
MyCol VARCHAR(10) DEFAULT '' NOT NULL
)

HTH|||Absolutley. Oracle's treatment of empty strings as nulls is not standard, and is a bad idea.|||Absolutley. Oracle's treatment of empty strings as nulls is not standard, and is a bad idea.This issue has been discussed a lot ;)
I think it's perfectly OK.|||The war in Iraq has been discussed a lot too. I guess that makes it OK as well?

A zero-length string is distinctly different that a null value. Anyone who equates the two does not understand the concept of a null value. A null value is "unknown" or "undetermined". A zero-length string has a known value. How can you possibly say that a known value is equal to some value that is not known? You can't. Hence, Oracle's implementation is flawed.|||zustimmung|||Anyone who equates the two does not understand the concept of a null valueI do understand the concept of a null value ;)

A zero-length string has a known value.And what is this "value"?
Can you have a similar distinction for a numeric column or a date column? (i.e. an "empty" value which is not null?)

The point is, that I think that an empty string is essentially the same as a null value. It means "I don't have data for it".

When it comes to other data types then nobody seems to want this distinction between an "empty" value and a null value. So what's so special about a character value that makes it different from other data types?|||The value of an empty string is 'empty'. The value of null is 'unknown'. There is a difference. (For one thing, one unknown value cannot be determined to be equal to another unknown value but 'empty' is definitely equal to 'empty'.)

FWIW our standard is to avoid nulls whenever an appropriate default value can be determined. Our standard default character value is the empty string so Oracle's null handling usually works for us on a practical level-but it's not ideal & definitely not ANSI-compliant. It'd be nice if you could turn that behavior on & off according to the database. Mostly I like defaulting nulls to empty strings & having a database setting for it would eliminate mistakes-but sometimes I *don't* want nulls to default to empty strings. Then, I'd like to be able to turn that 'automatic default' off.|||The value of an empty string is 'empty'. The value of null is 'unknown'.Then why doesn't anybody need this concept (empty vs. unknown) for non-character data types?
In my(!) opinion there is no difference between empty and unknown.|||I do understand the concept of a null value ;)
Ok maybe you do understand the concept of a null value. I'll give you the benefit of the doubt for now.

And what is this "value"?Zzzzzzt! Doubt removed!

The point is, that I think that an empty string is essentially the same as a null value. It means "I don't have data for it".Zzzzzzt again! A null value means you don't have data for it. A zero-length string means there is no data for it.

In my(!) opinion there is no difference between empty and unknown.

Then your opinion is wrong.

Here is an empty box: []
Here is a box which may be empty, but which may contain $1,000,000: []

Would you say the two are of equal value? Would you trade the box who's contents are unknown for the box which is known to be empty?

Well? Deal or no deal?

Do not feel bad. You are not alone. The concept of a NULL value is strangely difficult to grasp for many people. A common post of the SQL Server forums is something like "How come when I execute SELECT * FROM TABLE WHERE COLUMNVALUE = NULL I don't get any rows returned?"|||The concept really isn't empty vs. unknown, it's 'known' vs. unknown. Empty string just happens to be a convenient default (known) value for character data. The concept exists for non-character data too, it's just more difficult figuring out the appropriate default value. What's the appropriate default for Boolean?