CHARACTER_LENGTH scalar function

The schema is SYSIBM.

expression

An expression that returns a value of a built-in character or graphic string.

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 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".

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'	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 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.