Re: Is This the varchar2 ZLS Issue?

Tony Andrews wrote:

> All correct - for Oracle. But note that the ANSI standard is that the
> empty string is NOT null, and "where '' = ''" should always be TRUE.
> This Oracle anomaly is a trap for those used to other DBMSs that honour
> the ANSI standard on this point.
>

I used to think this also. But honestly, where do you use value ''? For example, we have attributes FIRST, MID and LAST for table EMPLOYEE meaning first name, middle initial and last name. It would be obvious to use '' in case employee does not have middle initial. This would differ from the case where employee does have middle initial but we do not know it. But, what would you do with this information? For example in reports, how would you differ these two employees?

And how would you make a form (for example a web form) to let employees enter and update their contact information? If he does not enter middle initial, should we assume that he does not have it? Or should we have a button, which is pressed if middle initial is missing?

One database where ''s are used almost criminally is GO (Gene Ontology), which is a MySQL database. There are lots of fields which are marked as NO NULL, but when there is no actual data, they have used ''s.

--
Arto Viitanen, CSC Ltd.
Espoo, Finland