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

When in default mode, the length of the string is measured in bytes. But when in Oracle mode, it’s measured in characters.

Syntax

The syntax goes like this:

LENGTH(str)

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

Example

Here’s a basic example:

SELECT LENGTH('café');

Result:

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

In this case, the string was four characters long, but LENGTH() returned 5.

This is because the last character uses two bytes, and I’m using the default SQL mode (my sql_mode=DEFAULT). When in default SQL mode, LENGTH() returns the number of bytes.

Oracle Mode

Switching to Oracle mode results in LENGTH() returning just the number of characters in the string (as opposed to the number of bytes).

Let’s switch our session to Oracle mode:

SET SESSION sql_mode='ORACLE';

And now let’s run the previous LENGTH() example again:

SELECT LENGTH('café');

Result:

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

This time it returns the number of characters (4) instead of the number of bytes (5).

When in Oracle mode, LENGTH() returns the same result as CHAR_LENGTH() and its synonym, CHARACTER_LENGTH().

Comparison with CHAR_LENGTH() and BIT_LENGTH()

Let’s switch back to default mode:

SET SESSION sql_mode=DEFAULT;

Here’s a quick comparison between LENGTH() when in default mode, CHAR_LENGTH() and BIT_LENGTH() which returns the number of bits in a string:

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

Result:

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

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

CHAR_LENGTH() returns 1, because it’s still just a single character, and BIT_LENGTH() returns the number of bits (24).

Again, if we’d been in Oracle mode, LENGTH() would have returned the same as CHAR_LENGTH().

Non-Strings

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

Here’s another example that uses a number:

SELECT LENGTH(1234);

Result:

+--------------+
| LENGTH(1234) |
+--------------+
|            4 |
+--------------+

Null Arguments

Passing null returns null:

SELECT LENGTH(null);

Result:

+--------------+
| LENGTH(null) |
+--------------+
|         NULL |
+--------------+

Missing Argument

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

SELECT LENGTH();

Result:

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