How the CHARACTER_LENGTH() Function Works in MySQL

In MySQL, the CHARACTER_LENGTH() function returns the length of a string, measured in characters.

CHARACTER_LENGTH() is a synonym for the CHAR_LENGTH() function.

Syntax

The syntax goes like this:

CHARACTER_LENGTH(str)

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

Example 1 – Basic Usage

Here’s an example of the basic usage:

SELECT CHARACTER_LENGTH('Cat');

And here’s the result:

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

Example 2 – Trailing Blanks

Note that CHARACTER_LENGTH() includes trailing blanks (e.g. spaces at the end of the string) in its calculations.

So if we add a space to the end of the previous example:

SELECT CHARACTER_LENGTH('Cat ');

Here’s the result:

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

But we can always remove that trailing space by using either the TRIM() function or the RTRIM() function:

SELECT 
  CHARACTER_LENGTH(TRIM('Cat ')) AS 'TRIM',
  CHARACTER_LENGTH(RTRIM('Cat ')) AS 'RTRIM';

Here’s the result:

+------+-------+
| TRIM | RTRIM |
+------+-------+
|    3 |     3 |
+------+-------+

Example 3 – Leading Blanks

The same concept applies to leading blanks. You can use either TRIM or LTRIM:

SELECT 
  CHARACTER_LENGTH(TRIM(' Cat')) AS 'TRIM',
  CHARACTER_LENGTH(LTRIM(' Cat')) AS 'LTRIM';

Result:

+------+-------+
| TRIM | LTRIM |
+------+-------+
|    3 |     3 |
+------+-------+

Example 4 – Data Types

It doesn’t matter what data type the string is stored as, it will still return the same result. This is in contrast to the LENGTH() function, which will return double the number of characters in cases where the data is being stored as a Unicode string.

In the following example, the ArtistName column uses varchar(255):

SELECT CHARACTER_LENGTH(ArtistName) Result
FROM Artists
WHERE ArtistName = 'Lit';

Here’s the result:

+--------+
| Result |
+--------+
|      3 |
+--------+

And if we modify the ArtistName column to use Unicode:

ALTER TABLE Artists 
MODIFY COLUMN ArtistName VARCHAR(255) unicode;

And run the same query again:

SELECT CHARACTER_LENGTH(ArtistName) 
FROM Artists
WHERE ArtistName = 'Lit';

We still get the same result:

+--------+
| Result |
+--------+
|      3 |
+--------+

However, if we had used the LENGTH() function, the result would be 6. This is because Unicode strings store 2 bytes per character, and the LENGTH() function returns the length measured in bytes.