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 |
+----------------+

Find the length of the longest string in MySQL

There are a number of string functions in MySQL for extracting text, working out the position of a substring, calulating the length of text and so on. This post looks at how to work out the length of the longest string in a field in a MySQL table.
The LENGTH() function in MySQL returns the length of a string in bytes. Multi-byte characters will count as multiple bytes. The examples in this post will use the LENGTH() function but you can substitute them with the CHAR_LENGTH() function instead if you want to count the number of characters rather than bytes. Note that CHAR_LENGTH will treat a character that uses two bytes a one single character and would return 1.
The first example below illustrates what LENGTH() will return:

SELECT LENGTH('this is a test');

This will return the number 14.
To work out the length of the largest string in a MySQL table, combine the LENGTH() and MAX() functions together like so:

SELECT MAX(LENGTH(field_to_query)) FROM table_to_query;

where "field_to_query" is the fieldname you want to query and table_to_query is the table. This will then return the length of the longest field named "field_to_query" as an integer.
This can be quite useful if you've loaded data from an external source into a table and want to ensure you have enough storage space for the data. I need to regularly load data from a text file into a MySQL table and one of the fields is labelled "long_description". Initially I created the field as a VARCHAR(255) and then ran the above query against the table only to discover the longest field(s) were 255 bytes long. I then changed the table to type TEXT and ran the query again and found out the longest field was acutally 1210 bytes long.
This final example will show all the unique lengths along with a count:

SELECT LENGTH(field_to_query), COUNT(*) 
FROM table_to_query GROUP BY LENGTH(field_to_query);

To order it by the those with the highest count first, add "ORDER BY COUNT(*) DESC" and to order it by the longest count first, add "ORDER BY LENGTH(field_to_query) DESC" e.g.:

SELECT LENGTH(field_to_query), COUNT(*) 
FROM table_to_query GROUP BY LENGTH(field_to_query) 
ORDER BY COUNT(*) DESC

SELECT LENGTH(long_description), COUNT(*) 
FROM table_to_query GROUP BY LENGTH(field_to_query) 
ORDER BY LENGTH(field_to_query) DESC

Example output for the first ten rows, ordered by the highest count first is as follows:

+------------------------+----------+
| LENGTH(field_to_query) | COUNT(*) |
+------------------------+----------+
|                    242 |      198 |
|                    644 |      170 |
|                    222 |      169 |
|                    180 |      151 |
|                    183 |      146 |
|                    176 |      146 |
|                    143 |      142 |
|                    201 |      142 |
|                    235 |      139 |
|                    218 |      138 |
+------------------------+----------+
10 rows in set (0.05 sec)
        

Related posts:

• Randomly ordering a MySQL result set
• String concatenation with MySQL
• Maximum length for MySQL TEXT field types
• Using SELECT REPLACE with MySQL
• Formatting Dates and Times with MySQL
• Cross Table Update with MySQL