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;
/