Обсуждение: Question
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) regards Robert
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. > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
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