MySQL String Length Function

MySQL provides us with a collection of tools and built-in functions for manipulating various data types and records in a database. Some common and useful functions are String functions and operators.

In this tutorial, we shall look at how to use pre-defined MySQL functions to determine the length of a string.

The Basics

Without diving deep into character sets, MySQL supports a wide selection of character sets. Each character set contains a maximum length of bytes it can store per character.

To learn more about the supported character set and their maximum length, use the resource provided below:

https://dev.mysql.com/doc/refman/8.0/en/charset-charsets.html

In MySQL, a string can be in any of the supported character sets. To determine the length of a string, you can use the bytes method or the character method.

The bytes method returns the length of the specified string in bytes. If a string contains a set of 1-byte characters, the length of the string is equal to the number of characters.

However, if the specified string contains multi-byte characters, the number of the characters is not equal to the length of the string.

Using MySQL CHARACTER_LENGTH()

To get the length of a string in characters, you can use the CHARACTER_LENGTH() function. This function is a synonym of the CHAR_LENGTH() function. It works by counting the number of characters in a string regardless of whether it’s a single-byte or multi-byte character.

Let us take the country table in the sakila database. To get the length of country name strings, we can use a query as:

SELECT country, char_length(country) AS length
FROM country
ORDER BY length
LIMIT 10;

The above query should return the length of country names.

Using MySQL LENGTH()

To get the length of a string in bytes, we use the LENGTH() function. In this case, the function takes into account whether it’s a single-byte or multi-byte character.

We can apply the above example as shown in the query below:

SELECT country, LENGTH(country) AS length
FROM country
ORDER BY length
LIMIT 10;

In this example, the values are in bytes instead of characters. Since the above examples use single-byte characters, you may not notice the difference.

However, on a multi-byte character as the Kanji value for the country, the values are different.

Consider the query below:

SELECT CHAR_LENGTH('国') AS length_in_char;

Once we run the above query, we should get the length as 1.

+----------------+
| length_in_char |
+----------------+
| 1 |
+----------------+

Consider the same query using the length() function.

SELECT LENGTH('国') AS length_in_bytes;

In this case, the length is 3.

+-----------------+
| length_in_bytes |
+-----------------+
| 3|
+-----------------+

Conclusion

This guide has shown you how to use the built-in MySQL functions to get the length of a string in bytes and characters.

Thank you for reading!

MySQL String Length Function

The Basics

Using MySQL CHARACTER_LENGTH()

Using MySQL LENGTH()

Conclusion

About the author

John Otieno