How CHAR_LENGTH() Works in MariaDB

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

Syntax

The syntax goes like this:

CHAR_LENGTH(str)

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

Example

Here’s a basic example:

SELECT CHAR_LENGTH('Splash');

Result:

+-----------------------+
| CHAR_LENGTH('Splash') |
+-----------------------+
|                     6 |
+-----------------------+

Multi-Byte Characters

The CHAR_LENGTH() function counts each character as a single character, regardless of how many bytes it uses. Therefore, characters that use two, three, or even four bytes will still be counted as one character.

This is in contrast to functions such as LENGTH(), which returns the number of bytes (unless it’s in Oracle mode, in which case it does the same as CHAR_LENGTH() – returns the number of characters). It’s also in contrast to functions like BIT_LENGTH() which returns the number of bits in a string.

Here’s an example that compares these functions:

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

Result:

+-------------------+--------------+------------------+
| CHAR_LENGTH('©')  | LENGTH('©')  | BIT_LENGTH('©')  |
+-------------------+--------------+------------------+
|                 1 |            2 |               16 |
+-------------------+--------------+------------------+

The copyright symbol (©) uses two bytes. CHAR_LENGTH() returns 1, because it’s still just a single character. LENGTH() and BIT_LENGTH() on the other hand, return the number of bytes and bits respectively.

This example was done using the default SQL_MODE. As mentioned, if it had been in Oracle mode, LENGTH() would have behaved like CHAR_LENGTH() and returned 1.

Null Arguments

Passing null returns null:

SELECT CHAR_LENGTH(null);

Result:

+-------------------+
| CHAR_LENGTH(null) |
+-------------------+
|              NULL |
+-------------------+

Missing Argument

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

SELECT CHAR_LENGTH();

Result:

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

Also see the CHARACTER_LENGTH() function, which is a synonym for CHAR_LENGTH().