The LENGTH() function in SQL

The semantics of the LENGTH() SQL function differs according to the database engine.

The SQL LENGTH() function must be used with care: Each database server has different semantics for this function, regarding length and trailing blanks handling.

The BDL language provides a LENGTH() built-in function, which is different from the SQL LENGTH() function, used in SQL statements. The LENGTH() function of BDL returns zero when the string expression is NULL.

Table 1. SQL LENGTH() support by database brand
Database Server Type Function name Counting unit Significant trailing blanks for CHAR() columns Return value when NULL Related topic
IBM® DB2® LUW
LENGTH(expr)
Octets Yes NULL See details
IBM Informix®
LENGTH(expr)
Octets No NULL Native SQL feature
IBM Netezza
LENGTH(expr)
Characters No NULL See details
Microsoft™ SQL Server
LEN(expr)
Characters No NULL See details
Oracle® MySQL / MariadDB
LENGTH(expr)
Characters No NULL See details
Oracle Database Server
LENGTH(expr)
Characters Yes NULL See details
PostgreSQL
LENGTH(expr)
Characters No NULL See details
SAP HANA®
LENGTH(expr)
Characters No NULL See details
SQLite
LENGTH(expr)
Characters Yes NULL See details
Search for LENGTH() usage in your SQL statements and review the code of the database-specific function.
Tip:
Create a user-defined SQL function that implements the Informix SQL LENGTH() function. For example, with Oracle PL/SQL:
CREATE OR REPLACE FUNCTION vlength(
    value IN VARCHAR2
    )
    RETURN INTEGER
AUTHID CURRENT_USER
IS
BEGIN
    RETURN NVL(LENGTH(RTRIM(value)),0);
END;
/