MariaDB LENGTHB() Explained

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

Syntax

The syntax goes like this:

LENGTHB(str)

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

Example

Here’s a basic example:

SELECT LENGTHB('café');

Result:

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

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

Comparison with CHAR_LENGTH() and BIT_LENGTH()

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

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

Result:

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

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

CHAR_LENGTH() returns 1, because it’s still just a single character, 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 LENGTHB(1234);

Result:

+---------------+
| LENGTHB(1234) |
+---------------+
|             4 |
+---------------+

Null Arguments

Passing null returns null:

SELECT LENGTHB(null);

Result:

+---------------+
| LENGTHB(null) |
+---------------+
|          NULL |
+---------------+

Missing Argument

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

SELECT LENGTHB();

Result:

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

LENGTHB() vs LENGTH()

The LENGTHB() function is similar to the LENGTH() function, except for how it works in Oracle mode.

The LENGTHB() function works the same way, regardless of which mode it’s in (i.e. it returns the number of bytes in the string).

LENGTH() on the other hand, behaves differently, depending on whether it’s in Oracle mode or default mode.

Specifically, when in default mode, LENGTH() measures the string in bytes. But when in Oracle mode, it measures the string in characters.