LENGTH scalar function

The LENGTH function returns the length of expression in the implicit or explicit string unit.

The schema is SYSIBM.

expression: An expression that returns a value that is a built-in data type. If expression can be null, the result can be null; if expression is null, the result is the null value.
CODEUNITS16, CODEUNITS32, or OCTETS: Specifies the string unit of the result. CODEUNITS16 specifies that the result is to be expressed in 16-bit UTF-16 code units. CODEUNITS32 specifies that the result is to be expressed in 32-bit UTF-32 code units. OCTETS specifies that the result is to be expressed in bytes.
If a string unit is explicitly specified, and if expression is not string data, an error is returned (SQLSTATE 428F5). If a string unit is specified as CODEUNITS16 or CODEUNITS32, and expression is a binary string or bit data, an error is returned (SQLSTATE 428GC). If a string unit is specified as OCTETS and expression is a binary string, an error is returned (SQLSTATE 42815). For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String units in built-in functions in Character strings.

If a string unit argument is not explicitly specified and if expression is a character or graphic string, the string units of expression determines the string unit that is used for the result. Otherwise, the value returned specifies the length in bytes.

The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

The length of character and graphic strings includes trailing blanks. The length of binary strings includes binary zeros. The length of varying-length strings is the actual length and not the maximum length. The length of all other values is the number of bytes used to represent the value:

2 for small integer (SMALLINT)
4 for large integer (INTEGER)
8 for big integer (BIGINT)
(p/2)+1 for decimal numbers with precision p
8 for DECFLOAT(16)
16 for DECFLOAT(34)
The length of the string for binary strings
The length of the string for character strings
4 for single-precision floating-point
8 for double-precision floating-point
4 for DATE
3 for TIME
7+(p+1)/2 for TIMESTAMP(p)

Examples

Example 1: Assume that the host variable ADDRESS is a varying-length character string with a value of '895 Don Mills Road'.
```
   LENGTH(:ADDRESS)
```
returns the value 18.
Example 2: Assume that START_DATE is a column of type DATE.
```
   LENGTH(START_DATE)
```
returns the value 4.
Example 3: The following example returns the value 10.
```
   LENGTH(CHAR(START_DATE, EUR))
```

Example 4: The following examples work with the Unicode string '&N~AB', where '&' is the musical symbol G clef character, and '~' is the combining tilde character. This string is shown in different Unicode encoding forms in the following example:

	'&'	'N'	'~'	'A'	'B'
UTF-8	X'F09D849E'	X'4E'	X'CC83'	X'41'	X'42'
UTF-16BE	X'D834DD1E'	X'004E'	X'0303'	X'0041'	X'0042'
UTF-32BE	X'0001D11E'	X'0000004E'	X'00000303'	X'00000041'	X'00000042'

Assume that the variable UTF8_VAR contains the UTF-8 representation of the string.

   SELECT LENGTH(UTF8_VAR, CODEUNITS16),
     LENGTH(UTF8_VAR, CODEUNITS32),
     LENGTH(UTF8_VAR, OCTETS)
   FROM SYSIBM.SYSDUMMY1

returns the values 6, 5, and 9, respectively.

Assume that the variable UTF16_VAR contains the UTF-16BE representation of the string.

   SELECT LENGTH(UTF16_VAR, CODEUNITS16),
      LENGTH(UTF16_VAR, CODEUNITS32),
      LENGTH(UTF16_VAR, OCTETS)
   FROM SYSIBM.SYSDUMMY1

returns the values 6, 5, and 12, respectively.