DB2 Scalar functions - CHARACTER_LENGTH

CHARACTER_LENGTH

The CHARACTER_LENGTH function returns the length, in the specified string unit, of an expression.

The schema is SYSIBM.

expression

An expression that returns a built-in character, binary, or graphic string, or a Boolean 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 specified as CODEUNITS16 or CODEUNITS32, and expression is a binary string or a FOR BIT DATA string, an error is returned (SQLSTATE 428GC).

If a string unit argument is not specified and expression is a character string that is not FOR BIT DATA or is a graphic string, the default is CODEUNITS32.
Otherwise, the default is OCTETS.

For more information, see "String units in built-in functions" in "Character strings".

Result

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 varying-length strings is the actual length and not the maximum length.

Examples

Assume that NAME is a VARCHAR(128) column, encoded in Unicode UTF-8, that contains the value 'Jürgen'.
The following two queries return the value 6:

   SELECT CHARACTER_LENGTH(NAME, CODEUNITS32)
   FROM   T1 
   WHERE  NAME = 'Jürgen'

   SELECT CHARACTER_LENGTH(NAME, CODEUNITS16) 
   FROM   T1 
   WHERE  NAME = 'Jürgen'

The following two queries return the value 7:

   SELECT CHARACTER_LENGTH(NAME, OCTETS)
   FROM   T1 
   WHERE  NAME = 'Jürgen'

   SELECT LENGTH(NAME)
   FROM   T1 
   WHERE  NAME = 'Jürgen'

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'	vX'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 CHARACTER_LENGTH(UTF8_VAR, CODEUNITS16)
   ,      CHARACTER_LENGTH(UTF8_VAR, CODEUNITS32)
   ,      CHARACTER_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 CHARACTER_LENGTH(UTF16_VAR, CODEUNITS16)
   ,      CHARACTER_LENGTH(UTF16_VAR, CODEUNITS32)
   ,      CHARACTER_LENGTH(UTF16_VAR, OCTETS)
   FROM   SYSIBM.SYSDUMMY1

returns the values 6, 5, and 12, respectively.

Result

Examples

Related reference