Ajit, > Why is ''(empty) not equal to null? Its a major headache when > porting > from other RDBMS like Oracle.
'' is not equal to NULL because that is the ANSI SQL92 and SQL99 international specification. The fact that other databases fail to follow the specification (and '' = NULL is *not* standard Oracle 8 behavior, either, so I don't know what you are porting from ... MS Access?) is not our concern. NULL is not equal to *anything*, including itself. Nor is it greater or less than anything. In fact, any operation involving NULL should result in NULL. This is the SQL spec, becuase NULL represents "unknown" and thus cannot be evaluated. For my intranet applications, I wrote a set of functions called "is_empty(data)" since my web programmer is rather liberal in substituting NULL for '' or for '0' or whatever. They go like this: CREATE FUNCTION is_empty( VARCHAR ) RETURNS BOOLEAN AS ' SELECT $1 IS NULL OR BTRIM($1) = ''; ' LANGUAGE 'sql' WITH (ISCACHABLE); CREATE FUNCTION is_empty( NUMERIC ) RETURNS BOOLEAN AS ' SELECT $1 IS NULL OR $1 = 0::NUMERIC; ' LANGUAGE 'sql' WITH (ISCACHABLE); etc. This will give you an all-purpose "empty value" detector. -Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
