Re: migrating from sql server 2000 to oracle 9i-r2

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Thu, 10 Mar 2005 20:27:13 +0000
Message-ID: <un91311i6ldvf1dass2v1v5ce72vgqd1uk@4ax.com>

On 10 Mar 2005 11:36:06 -0800, "rogergorden_at_gmail.com" <rogergorden_at_gmail.com> wrote:

>DA:
>
>Not to start a flame war but you wrote:
>
>7. NULL means NULL not an empty string

 But in Oracle, NULL as a string can mean an empty string, or can be NULL, depending on how you ask the question. Oracle's non-standard (but documented) behaviour here is the source of much confusion.

 Standard SQL behaviour defines the following:

''           is NOT NULL, equals ''
NULL         is NULL

length(NULL) is NULL

length('')   is NOT NULL, equals 0
'' || ''     is NOT NULL, equals ''
'' || 'x'    is NOT NULL, equals 'x'

NULL || 'x' is NULL

 Oracle behaves as follows, differences marked with ***:

''           is NULL ***
NULL         is NULL

length(NULL) is NULL
length('') is NULL ***
'' || '' is NULL ***
'' || 'x' is NOT NULL, equals 'x'
NULL || 'x' is NOT NULL, equals 'x' ***

>AFAIK:
>NULL != empty string

 Nothing != NULL; comparisons to NULL are UNKNOWN.

 Here's another, longer, thread on the same subject:

http://groups-beta.google.com/group/comp.databases.oracle.misc/browse_frm/thread/5195af7be8a95c4c/b8c2bac14df2aec7

 The underlying cause is that Oracle's internal representation of a zero-length string (stored as length of string followed by data - so a zero byte) is identical to the internal representation of a NULL (a zero byte).

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Thu Mar 10 2005 - 14:27:13 CST