LENGTH

The LENGTH function returns the length of a value.

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 is the length of the argument. 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:

The following example assigns the length of the string available in the column EMPL_NAME from the table EMPLOYEE to the variable NAME_LENGTH:

SELECT LENGTH(EMPL_NAME) AS NAME_LENGTH FROM EMPLOYEE

The above example returns the following.

NAME_LENGTH
6
6
5
3
6
6
5