Function group: numeric function
CHAR_LENGTH() or CHARACTER_LENGTH() determines the number of bytes or code units in a string.
{ CHAR_LENGTH | CHARACTER_LENGTH }( expression [USING [CODE_UNITS | OCTETS]])
expression
Alphanumeric expression or national expression. Its evaluation must return either an alphanumeric string (data type CHAR or VARCHAR) or a national string (data type NCHAR or NVARCHAR).
In the case of the alphanumeric data types CHAR and VARCHAR, CHAR_LENGTH( ) and OCTET_LENGTH( ) (see section "OCTET_LENGTH() - Determine string length" ) return the same values because each character is represented in precisely one byte (octet).
In the case of the national data types NCHAR and NVARCHAR the length can be determined either in bytes (OCTET_LENGTH and CHAR_LENGTH ... USING OCTETS functions) or in UTF-16 code units (CHAR_LENGTH ... USING CODE_UNITS function). A code unit in UTF-16 = 2 bytes. The number of Unicode characters in a national string can be less than the number of code units in UTF-16 as some Unicode characters are represented by two consecutive code units in UTF-16 (surrogate pairs).
expression may not be a multiple value with dimension > 1. See also section "Compatibility between data types".
USING CODE_UNITS
The length is to be output in code units.
In the data types CHAR and VARCHAR, 1 code unit = 1 byte.
In the data types NCHAR and NVARCHAR, 1 code unit = 2 bytes.
USING OCTETS
The length is to be output in bytes.
In the data types CHAR and VARCHAR, 1 character = 1 byte.
In the data types NCHAR and NVARCHAR, 1 character = 1 or 2 code units = 2 or 4 bytes respectively.
Result
If the string contains the NULL value, the result is the NULL value.
Otherwise:
The result is the number of bytes or code units in the string.
Data type: INTEGER
Examples
Determine the number of bytes (characters) contained in the alphanumeric string 'only' (result: 4).
CHAR_LENGTH ('only') USING OCTETS
Determine the number of bytes contained in the national string 'for' (result: 6).
CHAR_LENGTH (N'for') USING OCTETS
Determine the number of code units contained in the national string 'for' (result: 3).
CHAR_LENGTH (N'for') USING CODE_UNITS
Determine the number of code units contained in the national string 'München' (result: 7).
CHAR_LENGTH (U&'M\00FCnchen')