LENGTH

The LENGTH function returns the length of a value.

LENGTH(expression)

See CHARACTER_LENGTH, OCTET_LENGTH, and BIT_LENGTH for similar functions.

expression
An expression that returns a value of any built-in data type.

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 result is the length of the argument. The length of strings includes blanks. The length of a varying-length string is the actual length, not the length attribute.

The length of a graphic string is the number of double-byte characters (the number of bytes divided by 2). 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 integral part of (p/2)+1 for packed decimal numbers with precision p
  • p for zoned decimal numbers with precision p
  • 4 for single-precision float
  • 8 for double-precision float
  • 8 for DECFLOAT(16)
  • 16 for DECFLOAT(34)
  • The length of the string for strings
  • 3 for time
  • 4 for date
  • 7+(p+1)/2 for timestamp(p)
  • The actual number of bytes used to store the DataLink value (plus 19 if the DataLink is FILE LINK CONTROL and READ PERMISSION DB) for datalinks
  • 26 for row ID

Examples

  • Assume the host variable ADDRESS is a varying-length character string with a value of ‘895 Don Mills Road'.
      SELECT LENGTH(:ADDRESS)
        FROM SYSIBM.SYSDUMMY1
    Returns the value 18.
  • Assume that PRSTDATE is a column of type DATE.
      SELECT LENGTH(PRSTDATE)
        FROM PROJECT
    Returns the value 4.
  • Assume that PRSTDATE is a column of type DATE.
      SELECT LENGTH(CHAR(PRSTDATE, EUR))
        FROM PROJECT
    Returns the value 10.