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

CHARACTER_LENGTH() is actually a synonym for CHAR_LENGTH(), so you can use either.

Syntax

The syntax goes like this:

CHARACTER_LENGTH(str)

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

Example

Here’s a basic example:

SELECT CHARACTER_LENGTH('Cat');

Result:

+-------------------------+
| CHARACTER_LENGTH('Cat') |
+-------------------------+
|                       3 |
+-------------------------+

In this case, the string was three characters long, and so we got 3.

Non-Strings

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

Here’s another example that uses a number:

SELECT CHARACTER_LENGTH(1234);

Result:

+------------------------+
| CHARACTER_LENGTH(1234) |
+------------------------+
|                      4 |
+------------------------+

Comparison with CHAR_LENGTH()

As mentioned, CHARACTER_LENGTH() is a synonym for CHAR_LENGTH(). Here it is in comparison with CHAR_LENGTH():

SELECT 
    CHARACTER_LENGTH(1234),
    CHAR_LENGTH(1234);

Result:

+------------------------+-------------------+
| CHARACTER_LENGTH(1234) | CHAR_LENGTH(1234) |
+------------------------+-------------------+
|                      4 |                 4 |
+------------------------+-------------------+

Multi-Byte Characters

The CHARACTER_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 CHARACTER_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 
    CHARACTER_LENGTH('อ'),
    LENGTH('อ'),
    BIT_LENGTH('อ');

Result:

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

This Thai character (อ) uses 3 bytes. CHARACTER_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 CHARACTER_LENGTH() and returned 1.

Null Arguments

Passing null returns null:

SELECT CHARACTER_LENGTH(null);

Result:

+------------------------+
| CHARACTER_LENGTH(null) |
+------------------------+
|                   NULL |
+------------------------+

Missing Argument

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

SELECT CHARACTER_LENGTH();

Result:

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