The LENGTH() function

Informix^® provides the LENGTH() function:

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

Oracle has a equivalent function with the same name, but there is some difference:

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

With the Oracle LENGTH function, when using a CHAR column, values are always blank padded, so the function returns the size of the CHAR column. When using a VAR CHAR column, trailing blanks are significant, and the function returns the number of characters, including trailing blanks.

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

Since ORACLE handles empty strings ('') as NULL values, writing "LENGTH('')" is equivalent to "LENGTH(NULL)". In this case, the function returns NULL.

Solution

The ORACLE database interface cannot simulate the behavior of the Informix LENGTH() function.

You must check if the trailing blanks are significant when using the LENGTH() function.

If you want to count the number of character by ignoring the trailing blanks, you must use the RTRIM() function:

SELECT LENGTH(RTRIM(col1)) FROM table

SQL conditions which verify that the result of LENGTH() is greater that a given number do not have to be changed, because the expression evaluates to false if the given string is empty (NULL>n):

SELECT * FROM x WHERE LENGTH(col)>0

Only SQL conditions that compare the result of LENGTH() to zero will not work if the column is NULL. You must check your BDL code for such conditions:

SELECT * FROM x WHERE LENGTH(col)=0

In this case, you must add a test to verify if the column is null:

SELECT * FROM x WHERE ( LENGTH(col)=0 OR col IS NULL )

In addition, when retrieving the result of a LENGTH() expression into a BDL variable, you must check that the variable is not NULL.

In ORACLE, you can use the NVL() function in order to get a non-null value:

SELECT * FROM x WHERE NVL(LENGTH(c),0)=0

Informix Dynamic Server 7.30 supports the NVL() function, as in ORACLE. You can write the same SQL for both Informix 7.30 and ORACLE, as shown in this example.

If the Informix version supports stored procedures, you can create the following stored procedure in the Informix database in order to use NVL() expressions:

create procedure nvl( val char(512), def char(512) )
 returning char(512);
 if val is null then return def;
 else return val;
 end if;
end procedure;

With this stored procedure, you can write NVL() expressions like NVL(LENGTH(c),0). This should work in almost all cases and provides upward compatibility with Informix Dynamic Server 7.30.