Support ANSI SQL behaviour for null/empty Character string data types

Lukas Eder

Lukas Eder wrote: Again, it's a well-defined and useful concept in pretty much every other language / platform.

Are you sure?

Looking at MS ODBC documentation:

https://docs.microsoft.com/en-us/sql/odbc/microsoft/data-type-limitations

quote:

Zero-Length Strings When a dBASE, Microsoft Excel, Paradox, or Textdriver is used, inserting a zero-length string into a column actually inserts a NULL instead.

Interestingly, when googling a little, it's surprising to see that people are still raising issues about their queries not doing what they expect and finding it's because they are not catering for both null and empty string in those other database.

As someone who's spent a long time on the Oracle community, especially in the area of SQL and PL/SQL, I have to say it's rare to see questions that relate to people being confused over empty strings being null.  When we do get such questions, it's clearly people who've come from other databases and are extremely new to Oracle, but that is not very often.

Looking at MS ODBC documentation

We can discuss funky implementation details of old tech, or well-defined concepts in maths and computer science I see, you're biased by the first, I strive to achieve the latter.

When we do get such questions, it's clearly people who've come from other databases and are extremely new to Oracle, but that is not very often.

In other words: Oracle is an environment where people migrate away from, not to? ;-)

GregV

Having worked with the Oracle Database for quite some years now, I tend to agree with BluShadow. Making the distinction between empty string and null string would probably make things worse. The developers of the crappy third-party software we're implementing had a better idea: let's store a "null" value as 1 blank character ' '. Wonderful, now we have to use NVL(colname, ' ') = ' ' practically in all our queries...

Sven W.

Lukas Eder wrote: Again, it's a well-defined and useful concept in pretty much every other language / platform.

Are you sure?

Looking at MS ODBC documentation:

https://docs.microsoft.com/en-us/sql/odbc/microsoft/data-type-limitations

quote:

Zero-Length Strings When a dBASE, Microsoft Excel, Paradox, or Textdriver is used, inserting a zero-length string into a column actually inserts a NULL instead.

Interestingly, when googling a little, it's surprising to see that people are still raising issues about their queries not doing what they expect and finding it's because they are not catering for both null and empty string in those other database.

As someone who's spent a long time on the Oracle community, especially in the area of SQL and PL/SQL, I have to say it's rare to see questions that relate to people being confused over empty strings being null.  When we do get such questions, it's clearly people who've come from other databases and are extremely new to Oracle, but that is not very often.

BluShadow wrote:...As someone who's spent a long time on the Oracle community, especially in the area of SQL and PL/SQL, I have to say it's rare to see questions that relate to people being confused over empty strings being null. When we do get such questions, it's clearly people who've come from other databases and are extremely new to Oracle, but that is not very often.

I always find it strange how lengthy the discussions are in the Javascript community when the need to consider null, unkown, empty and zero length strings.

Here is such a fine example: https://stackoverflow.com/questions/154059/how-do-you-check-for-an-empty-string-in-javascript

I especially like this part of the discussion (which is totally confusing for sql/plsql developers):

	Huh? If you are expecting a string, empty(0) and empty(7) should return the same value. – Bennett McElwee Apr 1 '14 at 22:44
	In my particular case - empty("0") must return false (because that is a not empty string), but empty(0) must return true because it is empty – Andron Apr 2 '14 at 11:24
	But 0 isn't empty! It's a number, and numbers can't be full or empty. Of course, it's your function and so must satisfy your requirements, but empty is a misleading name in this case. – Bennett McElwee Apr 2 '14 at 21:15

The question for me is: Is there really an advantage to go this route? I firmly believe "no there is not". I know I'm pretty biased by my experience, but in this case, I do not see why we ever want to add more confusion. Especially since the only argument we had so far is to be more "standard compliant". If that is the only reason, then better change the standard.