LENGTH scalar function

The LENGTH function returns the length of a value.

The schema is SYSIBM.

The argument must be an expression that returns a value of any built-in data type that is not XML.

The result of the function is a large integer.

The result can be null; if the argument is null, the result is the null value.

The result is the length of the argument. The length does not include the null indicator byte of column arguments that allow null values. The length of strings includes blanks. The length of a varying-length string is the actual length, not the maximum length.

The length of a graphic string is the number of double-byte characters. Unicode UTF-16 data is treated as graphic data; a UTF-16 supplementary character takes two DBCS characters to represent and as such is counted as two DBCS characters.

The length of all other values is the number of bytes used to represent the value:

2 for small integer
4 for large integer
8 for big integer
The integer part of (p/2)+1 for decimal numbers with precision p
16 for DECFLOAT(34)
8 for DECFLOAT(16)
4 for single precision floating-point
8 for double precision floating-point
The length of the string for strings
4 for DATE
3 for TIME
10 for TIMESTAMP
12 for TIMESTAMP WITH TIME ZONE
7+((p+1)/2) for TIMESTAMP(p)
9+((p+1)/2) for TIMESTAMP(p) WITH TIME ZONE
The length of the row ID

Example 1: Assume that FIRSTNME is a VARCHAR(12) column that contains 'ETHEL' for employee 280. The following query returns the value 5:

   SELECT LENGTH(FIRSTNME)
     FROM DSN8C10.EMP
     WHERE EMPNO = '000280';

Example 2: Assume that HIREDATE is a column of data type DATE. Then, regardless of value the following statement returns the value 4:

   LENGTH(HIREDATE)

And the following function returns the value 10:

   LENGTH(CHAR(HIREDATE, EUR))