Question

Robert Partyka wrote:

Hi,

I have question:

why such condition:
foofield not like '%bar%'

where foofield is varchar

returns false (or rather even ignore row) on record where foofield is null
but
returns true on records where foofield is '' (empty string)

SQL specifications.

Empty string and NULL are two different thinks.

Regards
Gaetano Mendola

On Mon, 13 Oct 2003, Robert Partyka wrote:

why such condition:
foofield not like '%bar%'

where foofield is varchar

returns false (or rather even ignore row) on record where foofield is
null

Actually, it probably returns unknown(NULL) on such records.
NULL LIKE '%bar%' is unknown, so
NULL NOT LIKE '%bar%' is also unknown.

This is because NULL isn't the same as empty string, nor is it the absence
of a value, but it's an unknown value.

If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.

vhikida@inreach.com wrote:

If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.

Really? I don't believe it.

Regards
Gaetano Mendola

Gaetano Mendola wrote:

vhikida@inreach.com wrote:

If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.

Really? I don't believe it.

It is insane, but true.

Mike Mascari
mascarm@mascari.com

Mike Mascari <mascarm@mascari.com> writes:

Gaetano Mendola wrote:

vhikida@inreach.com wrote:

If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.

Really? I don't believe it.

It is insane, but true.

Uh, yeah, but I think that results in the same behaviour for the case at hand.

ie on oracle this is still holds:

NULL LIKE '%foo%' => NULL

The idiocy is that Oracle does this:

'' LIKE '%foo%' => NULL

because it treats '' as if you had NULL, ie, equivalent to the example above.

--
greg