Re: Oracle NULL vs '' revisited

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sun, 26 Aug 2007 15:17:24 +0200
Message-ID: <farugr$6nu$1@news5.zwoll1.ov.home.nl>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Martin T. wrote:

>
> The (for me) intuitive way:
> ---------------------------
> SELECT * from MY_TABLE
> WHERE LENGTH(VARCHAR_COLUMN) = LENGTH(:p_search_equal_length_string);

Actually, you measure what was entered into the column. "AAA" will return a length of 3. Now - what if nothing was filled in at the first place?

>
> The Oracle way:
> ---------------
> SELECT * from MY_TABLE
> WHERE LENGTH(VARCHAR_COLUMN) = LENGTH(:p_search_equal_length_string)
> OR (VARCHAR_COLUMN IS NULL AND :p_search_equal_length_string IS NULL);
>
> ... I'm not even sure that oracle statement does what it's supposed to do.
>
> Too far fetched?

Not from where I live. Again, what if nothing was filled in into your character column? Would that be an empty string, or just unknown?

NULL <> NULL, but '' = '', if that would help. The length of NULL is NULL again.
NULL is the unknown, the Amazon logic.
(Yup - it was *not* invented by Oracle!) - --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFG0X1kLw8L4IAs830RAnqhAKCdBHURXm/UZvwD+eCoSh2bQkUg9QCfZcWT EOvW9Zz7QsSmY3YcQLXVjK0=
=A0pM
-----END PGP SIGNATURE----- Received on Sun Aug 26 2007 - 08:17:24 CDT