What is LENGTH() in MySQL?

One of the many functions in MySQL is the LENGTH() function, which returns the length of a string, measured in bytes.

Example:

SELECT LENGTH('Lit');

Result:

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

This is a simple example and the result is the same as if we’d used the CHAR_LENGTH()function. However, the LENGTH() function can return different results, depending on the data type.

Data Types

When you query a database, the LENGTH() function can return a different result, depending on the data type. Unicode strings return double the number of bytes. UTF-8 strings can vary.

Here’s an example of using UTF-8:

SELECT LENGTH(_utf8 '€');

Results:

+---------------------+
| LENGTH(_utf8 '€')   |
+---------------------+
|                   3 |
+---------------------+

In this case, the Euro sign uses 3 bytes.

In the following example, we query a database. In this case, the ArtistName column uses varchar(255) data type:

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

So the result looks like this:

+--------------------+
| LENGTH(ArtistName) |
+--------------------+
|                  3 |
+--------------------+

However, if we change the column to ucs2, each character will be represented by a 2 byte Unicode code, and therefore the result will be different.

To demonstrate, we can alter the table like this:

SELECT LENGTH(ArtistName) 
ALTER TABLE Artists 
MODIFY COLUMN ArtistName VARCHAR(255) CHARSET ucs2;

Now if we query it again:

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

Result:

+--------------------+
| LENGTH(ArtistName) |
+--------------------+
|                  6 |
+--------------------+

Trailing Blanks

The LENGTH() function counts trailing blanks (such as spaces at the end of the string). So if we add a space to the end of the first example, we get the following result:

SELECT LENGTH('Lit ');

Result:

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

Leading Blanks

We get the same result with leading blanks (e.g. spaces at the start of the string):

SELECT LENGTH(' Lit');

Result:

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