LENGTH scalar function

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 428GC). 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 is not explicitly specified, the data type of the result determines the unit that is used. If the result is graphic data, the value returned specifies the length in 2-byte units. 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.