What is CHAR_LENGTH() in MySQL?

In MySQL, the CHAR_LENGTH() function returns the length of a string, measured in characters. MySQL also has CHARACTER_LENGTH(), which is a synonym for CHAR_LENGTH().

Here’s an example:

SELECT CHAR_LENGTH('Lit');

And here’s the result:

+--------------------+
| CHAR_LENGTH('Lit') |
+--------------------+
|                  3 |
+--------------------+

Trailing Blanks

Note that CHAR_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 CHAR_LENGTH('Lit ');

Here’s the result:

+---------------------+
| CHAR_LENGTH('Lit ') |
+---------------------+
|                   4 |
+---------------------+

But we can always remove that trailing space by adding the TRIM() function into the mix:

SELECT CHAR_LENGTH(TRIM('Lit '));

Here’s the result:

+---------------------------+
| CHAR_LENGTH(TRIM('Lit ')) |
+---------------------------+
|                         3 |
+---------------------------+

Leading Blanks

It’s the same thing with leading blanks. So if we add a space to the start of the string instead:

SELECT CHAR_LENGTH(' Lit');

We get the same result:

+---------------------+
| CHAR_LENGTH(' Lit') |
+---------------------+
|                   4 |
+---------------------+

Data Types

It doesn’t matter what data type the string is stored as, it will still return the same results. 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 CHAR_LENGTH(ArtistName) 
FROM Artists
WHERE ArtistName = 'Lit';

Here’s the result:

+-------------------------+
| CHAR_LENGTH(ArtistName) |
+-------------------------+
|                       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 CHAR_LENGTH(ArtistName) 
FROM Artists
WHERE ArtistName = 'Lit';

We still get the same result:

+-------------------------+
| CHAR_LENGTH(ArtistName) |
+-------------------------+
|                       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.