Re: Oracle NULL vs '' revisited

On Aug 17, 10:49 am, Matthew Harrison <m.harris..._at_craznar.com> wrote:
> Ok, I'm developing another oracle application where the distinction
> between NULL and '' will be important.
>
> Given Oracle doesn't adhere to SQL standards for the distinction, is
> there any best practices for comparing, and storing blank strings in a
> table where the field is conceptually NOT NULL.
>
> Thank you.
>
> --
> Pinging self [127.0.0.1] with 32 bites of banana cake:
>
> Ping statistics for 127.0.0.1:
> Slices: Sent = 4, Received = 0, Lost = 4 (100% loss),

I have always had difficulty with the concept that an empty string should not be considered a NULL value to begin with. What does an empty string hold? Oh wait, there is a binary zero used to indicate that the last user data value preceeds the binary zero. And if you compare two binary zeroes you get an equality so two empty strings are considered equal, but logically if the value is unknown there is no way determine if A = b, A < B, or A > B.

The string construct was a way for the developers of C to deal with the fact the processor they were using did not have instructions that delt with character data very well. Now we are apparently stuck with it.

For the most part when dealing with character data that could be unknown I have not found NULLs to be a problem even when the null had to be transferred into a string variable in a program. The database behaves by one set of rules and the program by another. If you need to treat nulls in a specific column as equal (emtpy strings) then you can use the NVL function in your where clause conditions, the is null and is not null conditions, and/or an additional attribute column to indicate empty where the column could also be NULL to handle the situation.

IMHO -- Mark D Powell -- Received on Fri Aug 17 2007 - 11:39:00 CDT