How the OCTET_LENGTH() function works in Mariadb?

The OCTET_LENGTH() function is a built-in function in Mariadb that returns the number of bytes in a string value.

Posted on

The OCTET_LENGTH() function is a built-in function in Mariadb that returns the number of bytes in a string value. The function is useful for measuring the size of a string in terms of bytes, which may differ from the number of characters depending on the character set and encoding. The function is also known as BYTE_LENGTH() or LENGTHB().

Syntax

The syntax of the OCTET_LENGTH() function is as follows:

OCTET_LENGTH(str)

Where str is a string value. If str is NULL, the function returns NULL.

Examples

Example 1: Getting the octet length of a simple string

The following example shows how to use the OCTET_LENGTH() function to get the number of bytes in a simple string:

SELECT OCTET_LENGTH('Hello') AS OctetLength;

The output is:

+-------------+
| OctetLength |
+-------------+
|           5 |
+-------------+

The function returns 5, which is the number of bytes in the string ‘Hello’. This is the same as the number of characters, since each character in the string is encoded with one byte in the default character set (latin1).

Example 2: Getting the octet length of a string with special characters

The following example shows how to use the OCTET_LENGTH() function to get the number of bytes in a string with special characters:

SELECT OCTET_LENGTH('你好') AS OctetLength;

The output is:

+-------------+
| OctetLength |
+-------------+
|           6 |
+-------------+

The function returns 6, which is the number of bytes in the string ‘你好’. This is different from the number of characters, which is 2, since each character in the string is encoded with three bytes in the default character set (utf8).

Example 3: Getting the octet length of a string with different character sets

The following example shows how to use the OCTET_LENGTH() function to get the number of bytes in a string with different character sets:

SELECT OCTET_LENGTH(_latin1'Hello') AS OctetLengthLatin1,
       OCTET_LENGTH(_utf8'Hello') AS OctetLengthUtf8,
       OCTET_LENGTH(_utf8mb4'Hello') AS OctetLengthUtf8mb4;

The output is:

+-------------------+-----------------+--------------------+
| OctetLengthLatin1 | OctetLengthUtf8 | OctetLengthUtf8mb4 |
+-------------------+-----------------+--------------------+
|                 5 |               5 |                  5 |
+-------------------+-----------------+--------------------+

The function returns the same number of bytes for all the strings, which is 5, since the string ‘Hello’ does not contain any characters that require more than one byte in any of the character sets. The _latin1, _utf8, and _utf8mb4 prefixes are used to specify the character set of the string literals.

There are some other functions in Mariadb that are related to the OCTET_LENGTH() function. They are:

  • CHAR_LENGTH(): This function returns the number of characters in a string value. The function is also known as LENGTH() or CHARACTER_LENGTH(). The function takes into account the character set and encoding of the string, and may return a different value than the OCTET_LENGTH() function. For example, the string ‘你好’ has a CHAR_LENGTH() of 2 and an OCTET_LENGTH() of 6 in the default character set (utf8).
  • BIT_LENGTH(): This function returns the number of bits in a string value. The function is equivalent to multiplying the OCTET_LENGTH() function by 8. For example, the string ‘Hello’ has a BIT_LENGTH() of 40 and an OCTET_LENGTH() of 5 in the default character set (latin1).
  • SPACE(): This function returns a string of spaces with a specified length. The length is measured in characters, not bytes. The function is useful for padding or aligning strings.

Conclusion

The OCTET_LENGTH() function is a useful function in Mariadb that allows you to get the number of bytes in a string value. The function is helpful for measuring the size of a string in terms of bytes, which may differ from the number of characters depending on the character set and encoding. You can also use other functions like CHAR_LENGTH(), BIT_LENGTH(), and SPACE() to manipulate strings in different ways. I hope this article helped you understand how the OCTET_LENGTH() function works in Mariadb.