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)