Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle NULL vs '' revisited

Re: Oracle NULL vs '' revisited

From: William Robertson <>
Date: Mon, 27 Aug 2007 01:30:57 -0700
Message-ID: <>

On Aug 26, 7:42 pm, "David Portas"
<> wrote:
> "DA Morgan" <> wrote in message
> > William Robertson wrote:
> >> On Aug 26, 2:17 pm, Frank van Bortel <>
> >> wrote:
> >>> NULL <> NULL, but '' = '', if that would help.
> >>> The length of NULL is NULL again.
> >> If we had a '' in Oracle then its length would be 0, but we don't,
> >> which is the subject of this thread. I was hoping someone could
> >> provide an example of non-null '' being a useful value.
> Here are two examples, one real and one hypothetical.
> I used to work for an audit firm. One of our services was to analyse
> accounts payable data to look for duplicate payments. In support of this,
> various statistics and exception reports were produced from the clients'
> financial systems. Typically, the financial systems we dealt with
> implemented the business rule that the combination of payee and document
> number (such as an invoice, debit or credit note) had to be unique. So the
> only way for a document to be processed twice was if the document was
> entered with a different number, perhaps due to a keying error or due to an
> operator forcing a payment through by modifying the number in some way, for
> example by entering "123A" instead of "123".
> As part of the audit, one of the transforms we did was to "clean" invoice
> and credit note numbers by removing non-numeric characters from the unique
> document number - so "123A" would become "123" and could be matched
> automatically with any similarly numbered document. Inevitably there was a
> lot of surplus "noise" generated by this technique - we were usually dealing
> with millions of invoices and many false matches were unavoidable - but it
> did produce the valid result that the column containing a cleaned document
> number would be blank if the original didn't contain any numeric characters.
> Those cases were probably of no interest most of the time but we certainly
> wouldn't be allowed to exclude them. Converting them to nulls wouldn't be
> acceptable either because the consumers of the data were accountants, who
> probably didn't know what a null was. And why should they? They just wanted
> to peform their usual queries and analysis, which might well include joins
> on the cleaned number column. Nulls would have been nothing like equivalent
> to using an empty string and would have added needless complexity.
> Now the hypothetical scenario. User names and passwords stored in a table
> and used for ODBC / OLEDB connection strings. Depending on the provider, a
> password may have to be supplied, even if it is a blank one. I do not
> advocate using blank passwords, but a blank password may be necessary or
> unavoidable in some cases (for example over a trusted connection, where the
> target system can't support the appropriate password management). Why should
> I be forced to make the password column nullable when the valid and correct
> value is a zero-length string?
> > In the end you only have three values, NULL, 0
> > and > 0. There are a nearly unlimited ways to code this in any
> > language and in any tool.
> I agree (I assume 0 refers to a zero-length string). I don't agree that
> zero-length strings are "empty" or not useful or that nulls are always an
> acceptable substitute.
> --
> David Portas

Thanks for the examples. I have to say they aren't as everyday as no- -name guy with his two names and his three name lengths. OK, given a business requirement to strip non-numeric characters from mandatory document IDs such as '123A', what do you do with document 'Z'? I can imagine going back to the business and explaining that if there are only non-numeric characters, that won't leave anything to identify the document by. Surely even an accountant would appreciate that problem? I would want to replace the ID with 'Audit 2007' or some similar code (presumably unique?) to flag them explicitly.

With the password example, surely when the designers of the system made the password a not-null column the intention was to force all users to provide a password, and allowing '' subverts that rule. If you are allowed to enter '' as a password, why have a mandatory constraint in the first place? Unless of course you are stuck with a password validator that inconveniently requires you to enter one.

I'm afraid both examples seem like workarounds to bad data or a design you can't change. Yes, if the system allows empty strings you might use them in this sort of situation, but if not it doesn't seem such a big deal.

At the risk of returning to the OP's original problem, it seemed to be something to do with distinguishing between myproc(''), myproc() and myproc(NULL), to which there are solutions in PL/SQL as I illustrated. Received on Mon Aug 27 2007 - 03:30:57 CDT

Original text of this message