Empty character strings

Informix®

Informix SQL consisders empty strings ( '' or "" ) as a non-NULL string with a length of zero.

Note:

In Genero BDL, when setting a variable with an empty string constant, it is automatically set to a NULL value:

DEFINE x char(10)
LET x = ""
IF x IS NULL THEN -- evaluates to TRUE
   ...
END IF

ORACLE

Oracle® SQL considers empty strings ('') as NULL.

Using literal string values that are empty ('') for INSERT or UPDATE statements will result in the storage of NULL values with Oracle, while Informix stores the value as a string with a length of zero:

INSERT INTO tab1 ( col1, col2 ) VALUES ( NULL, '' )

Using comparison operators (col='') with Oracle makes no sense, because an empty string is equivalent to NULL: The correct SQL expression is (col IS NULL).

SELECT * FROM tab1 WHERE col2 IS NULL

Solution

To increase portability, it is recommended that you avoid the usage of literal string values with a length of zero in SQL statements. Instead, use the NULL constant, or program variables.