How OCTET_LENGTH() Works in MariaDB

by

In MariaDB, OCTET_LENGTH() is a built-in string function that returns the length of the given string argument, measured in octets (bytes).

OCTET_LENGTH() is a synonym for LENGTHB().

It’s also a synonym for LENGTH() when not in Oracle mode (LENGTH() behaves differently in Oracle mode).

Syntax

The syntax goes like this:

OCTET_LENGTH(str)

Where str is the string for which the length will be returned.

Example

Here’s a basic example:

SELECT OCTET_LENGTH('café');

Result:

+-----------------------+
| OCTET_LENGTH('café')  |
+-----------------------+
|                     5 |
+-----------------------+

In this case, the string was four characters long, but OCTET_LENGTH() returned 5. This is because the last character uses two octets/bytes.

Comparison with CHAR_LENGTH() and BIT_LENGTH()

Here’s a quick comparison between OCTET_LENGTH(), CHAR_LENGTH() (which returns the number of characters in the string) and BIT_LENGTH() (which returns the number of bits in the string):

SELECT 
    OCTET_LENGTH('อ'),
    CHAR_LENGTH('อ'),
    BIT_LENGTH('อ');

Result:

+---------------------+--------------------+-------------------+
| OCTET_LENGTH('อ')   | CHAR_LENGTH('อ')   | BIT_LENGTH('อ')   |
+---------------------+--------------------+-------------------+
|                   3 |                  1 |                24 |
+---------------------+--------------------+-------------------+

This Thai character (อ) uses 3 bytes, and therefore OCTET_LENGTH() returns 3.

CHAR_LENGTH() returns the number of characters (1), and BIT_LENGTH() returns the number of bits (24).

Non-Strings

If the argument is not a string, it’s converted to a string.

Here’s another example that uses a number:

SELECT OCTET_LENGTH(1234);

Result:

+--------------------+
| OCTET_LENGTH(1234) |
+--------------------+
|                  4 |
+--------------------+

Null Arguments

Passing null returns null:

SELECT OCTET_LENGTH(null);

Result:

+--------------------+
| OCTET_LENGTH(null) |
+--------------------+
|               NULL |
+--------------------+

Missing Argument

Calling OCTET_LENGTH() without passing an argument results in an error:

SELECT OCTET_LENGTH();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'OCTET_LENGTH'