OCTET_LENGTH function - Amazon Redshift

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 | +--------------+