The LENGTH() function

Informix®

Informix provides the LENGTH() function to count the number of bytes of a character string expression:

SELECT LENGTH("aaa"), LENGTH(col1) FROM table 

Informix LENGTH() does not count the trailing blanks for CHAR or VARCHAR expressions, while Oracle counts the trailing blanks.

Informix LENGTH() returns 0 when the given string is empty. That means, LENGTH('')=0.

Netezza®

Netezza supports LENGTH() and CHARACTER_LENGTH() functions, but these count the number of characters (not bytes), and trailing blanks are significant.

The Netezza LENGTH() function ignores trailing blanks.

Netezza returns NULL if the LENGTH() parameter is NULL.

Solution

Review the program logic and make sure you do not pass NULL values to the LENGTH() SQL function.

The translation of LENGTH() expressions can be controlled with the following FGLPROFILE entry:
dbi.database.dsname.ifxemul.length = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.