OCTET_LENGTH function

Returns the length of the specified string as the number of bytes.

Syntax

OCTET_LENGTH(expression)

Argument

expression

A CHAR string, a VARCHAR string, a VARBYTE expression, or an expression that implicitly evaluates to a CHAR, VARCHAR, or VARBYTE type.

Return type

INTEGER

The OCTET_LENGTH function returns an integer indicating the number of bytes in the input string.

If the input string is a character string, the LEN function returns the actual number of characters in multi-byte strings, not the number of bytes. For example, a VARCHAR(12) column is required to store three four-byte Chinese characters. The OCTET_LENGTH function will return 12 for that string, and the LEN function will return 3 for that same string.

Usage notes

If expression is a CHAR string, the function returns the length of the CHAR string. For example, the output of a CHAR(6) input is a CHAR(6).

If expression is a VARCHAR string, trailing spaces are counted.

Examples

To return the number of bytes when the string francais with three trailing spaces is cast to a CHAR and a VARCHAR type, use the following example. For more information, see the CAST function.

SELECT OCTET_LENGTH(CAST('francais   ' AS CHAR(15))) AS octet_length_char, OCTET_LENGTH(CAST('francais   ' AS VARCHAR(15))) AS octet_length_varchar;

+-------------------+----------------------+
| octet_length_char | octet_length_varchar |
+-------------------+----------------------+
|                15 |                   11 |
+-------------------+----------------------+

To return the number of bytes and the number of characters in the string français, use the following example.

SELECT OCTET_LENGTH('français'), LEN('français');

+--------------+-----+
| octet_length | len |
+--------------+-----+
|            9 |   8 |
+--------------+-----+

To return the number of bytes when the string français is cast as a VARBYTE, use the following example.

SELECT OCTET_LENGTH(CAST('français' AS VARBYTE));

+--------------+
| octet_length |
+--------------+
|            9 |
+--------------+