"Ed Prochak" <edprochak_at_gmail.com> wrote in message
news:1188236996.185880.272230_at_50g2000hsm.googlegroups.com...
> On Aug 26, 2:42 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
>> "DA Morgan" <damor..._at_psoug.org> wrote in message
>>
>> news:1188150665.582294_at_bubbleator.drizzle.com...
>>
>> > William Robertson wrote:
>> >> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
>> >> 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
>
> David,
>
> I notice in both descriptions you mention "blank" results or data
>
>> 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.
> Now in this case you likely really do end up with '' (as opposed to '
> '), but the intention is the same. a cleaned value implies a result
> that is blank. (Consider a CHAR versus VARCHAR2 datatype). So you
> might need some extra code to deal with this case, but you want a
> blank not an "empty" string.
>
>
>> password may have to be supplied, even if it is a blank one. I do not
> And here it is pretty much how the UI might be coded. If the user
> merely presses <enter> at the password field, the error back to him
> would be something like: Password Required. Eventually they would
> enter a blank (' ') and the UI is happy, and the DB is happy (you can
> encrypt ' ' easily).
>
>
> I understand the frustation. If you are a UNIX programmer, used to
> things like /dev/null (such a wonderful creation), then you just want
> '' to be there. But notice, even in C from the user interface you will
> not really get just "" as the input. There is no input until the user
> presses <enter>, so the raw input always contains at least "\n". That
> \n might be stripped out but it was there.
>
> So I still don't see much use for '' except for maintaining some
> mathematical properties on strings (' ' is a poor identity operand).
>
> Ed
>
I'm not denying the obvious fact that a zero-length string value could be
coded in other ways (just as it would be possible to devise alternative
representations for 0 or 1 in a system that didn't support those integer
values). My only point was to show that there may be valid and sensible
reasons to wish to store zero-length strings. Using Oracle means that some
compromises are necessary, whereas in other languages or in DBMSs that
follow the SQL standard for strings those compromises are unnecessary.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Received on Mon Aug 27 2007 - 15:03:09 CDT
