MariaDB LENGTH() vs LENGTHB(): What’s the Difference?

Ever since version 10.3.1, MariaDB has included both a LENGTH() function and a LENGTHB() function.

That second one has a B at the end of the name. So it’s kind of like Length A and Length B, except that Length A doesn’t have the A.

Confused?

I was, when I first encountered LENGTHB(). I already knew about LENGTH(), so why the need for a “B” version?

Let’s find out.

Oracle Compatibility

According to MariaDB issue 12783, before LENGTHB() was introduced (and before LENGTH() was modified) things worked like this:

MariaDB translates function LENGTH() to SQL Standard function OCTET_LENGTH().
Oracle translates function LENGTH() to SQL Standard function CHAR_LENGTH().

The decision was then made to change MariaDB’s LENGTH() function so it behaves differently, depending on what SQL mode it’s running in. Specifically:

When running in default mode (i.e. sql_mode=DEFAULT), MariaDB will continue to translate LENGTH() to OCTET_LENGTH().
However, when running in Oracle mode (i.e. sql_mode=ORACLE), it translates LENGTH() to CHAR_LENGTH() instead.

Introducing `LENGTHB()`

Which brings us to the LENGTHB() function.

The LENGTHB() function was added as part of the same work.

LENGTHB() is a synonym for OCTET_LENGTH() regardless of the SQL mode. In other words, LENGTHB() translates to OCTET_LENGTH() when sql_mode=DEFAULT and when sql_mode=ORACLE.

This enables us to use LENGTHB() in our code without worrying about it being affected by the user’s sql_mode settings.

The Difference

The difference between these two functions is outlined in the following table.

Function	Default Mode	Oracle Mode
`LENGTH()`	Returns the number of bytes.	Returns the number of characters.
`LENGTHB()`	Returns the number of bytes.	Returns the number of bytes.

Note that this difference is only present from MariaDB 10.3.1. Prior to that, LENGTHB() doesn’t exist, and LENGTH() simply translates to OCTET_LENGTH().

Example

Here’s an example that demonstrates the difference between LENGTH() and LENGTHB().

Let’s set our session to use the default mode:

SET SESSION sql_mode=DEFAULT;

My session was probably already in the default mode, but there’s no harm in explicitly setting it.

Now let’s run LENGTH() and LENGTHB() with the same argument:

SELECT 
    LENGTH('café'),
    LENGTHB('café');

Result:

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

So, when in default mode, they both return the same value.

In this case, they both returned 5, because there are 5 bytes in that string (the é character uses 2 bytes, and all others use 1 byte each).

Now let’s switch over to Oracle mode:

SET SESSION sql_mode=ORACLE;

Now let’s rerun the above statement:

SELECT 
    LENGTH('café'),
    LENGTHB('café');

Result:

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

This time there’s a difference between the two functions. This time LENGTH() returned 4. That’s 1 less than previously.

This is because LENGTH() behaves differently in Oracle mode. As mentioned, when sql_mode=ORACLE, the LENGTH() function translates to CHAR_LENGTH(), which returns the number of characters – not bytes.

In the previous example, LENGTH() returned the number of bytes because, when sql_mode=DEFAULT, it translates to OCTET_LENGTH().

Oracle Compatibility

Introducing LENGTHB()

The Difference

Example

Introducing `LENGTHB()`