In this tutorial, we shall look at how to use pre-defined MySQL functions to determine the length of a string.
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:
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:
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:
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:
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.
In this case, the length is 3.
| length_in_bytes |
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!