NULL != space (Was: Re: Informix Certified Professional Program)

>From: walt@wst.b30.ingr.com (Walter S. Tyszka) >Date: Tue, 11 Feb 97 12:24:37 CST >X-Informix-List-Id: <list.13116> > >} I On 6 Feb 1997 13:48:21 -0500, >} David_Coburn/ISG/WVUS/WorldVision_at_WVUS-NOTES@wvccg.wvus.org (David >} Coburn/ISG/WVUS/WorldVision) wrote: >} >< STUFF DELETED HERE > >} >} I reviewed what was likely one of the early tests which asserted, >} among other things that a space and a NULL are equivalent. [...] >} >Do you mean to say that a space and a NULL are NOT equivalent? > >According to Informix: NULL means unknown, so comparing anything to NULL >will evaluate as true - since you can not tell for sure whether the two >values are different. Quote me a manual name, version and page reference where Informix says that! Paraphrasing your statement unmercifully but correctly: * NULL means unknown, so comparing anything to NULL will evaluate to UNKNOWN (not TRUE), because you cannot tell for sure whether the two values are different. This has consequences: 1. A space is not a null string -- though it is impossible to tell the difference on most screen layouts. 2. In I4GL (or NewEra), the following code executes the branches as shown: DEFINE s1 CHAR(5) DEFINE s2 CHAR(5) LET s1 = " " LET s2 = NULL IF s1 = s2 THEN -- This branch is not executed ELSE -- This branch is executed END IF IF s1 != s2 THEN -- This branch is not executed ELSE -- This branch is executed END IF So, to answer your question, yes, I do mean that a space and a NULL are NOT equivalent? What could be causing your confusion is the way that I4GL treats the empty quoted string -- LET s1 = "" In SQL statements (eg the VALUES clause of an INSERT statement), the empty quoted string is equivalent to a single space in a quoted string. In I4GL code, the empty quoted string is equivalent to NULL -- much to everybody's confusion: MAIN DEFINE s CHAR(5) LET s = "" DISPLAY "s = <<", s, ">>" IF s IS NULL THEN DISPLAY "s is null" ELSE DISPLAY "s is not null" END IF END MAIN Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>