SQL LENGTH scalar function in DB2 for i SQL

SQL LENGTH scalar function in DB2 for i SQL

LENGTH

The LENGTH function returns the length of a value.

Syntax

LENGTH(expression)

The expression is a value of any built-in data type other than XML. The result can be null if the argument is null.

The length of character 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.

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

Small integer = 2

Large integer = 4

Big integer = 8

Decimal number = The integer part of (precision/2)+1 for decimal numbers

DECFLOAT(34) = 16

DECFLOAT(16) = 8

Single precision floating-point = 4

Double precision floating-point = 8

DATE = 4

TIME = 3

TIMESTAMP = 10

TIMESTAMP WITH TIME ZONE = 12

Row ID = The length of the row ID

Example

SELECT length(current_date),         
 length(current_time),               
 length(current_timestamp),          
 length(cast('TEST' as char(10))),   
 length(cast('TEST' as varchar(10))),
 length(1),                          
 length(554654545456),               
 length(50551.5)                     
  FROM sysibm.SYSDUMMY1              

Output:

       LENGTH          LENGTH          LENGTH          LENGTH          LENGTH    LENGTH ( 1 )          LENGTH   LENGTH ( 50551.5 )
            4               3              10              10               4               4               8                  4