MySQL String Length | MySQL Tutorials

MySQL provides 2 functions for determining string length. These functions are CHAR_LENGTH and LENGTH. Which of these functions you use depends on whether you want the number of characters in the string or the number of bytes in the string.

The CHAR_LENGTH function returns the total number of characters in a string, while the LENGTH function returns the total number of bytes in the string. Note this subtle difference!

Remember that multi-byte characters exist and one character may not necessary be one byte in size.

The remainder of this MySQL string length tutorial explains.

MySQL String Length Example

Look at and test the following examples of getting the length of a string in MySQL:

SELECT LENGTH('text'); -- gives 4, it is a 4 byte string
SELECT CHAR_LENGTH('text'); -- gives 4, it is a 4 character string

For the vast majority of applications, both functions will return the same result, but both functions are by no means to be used interchangeably.

Take care to determine if the string length you want to check is the number of characters in the string or the number of bytes in the string.

Multi-byte character Example showing the difference in the MySQL String Length Functions

Have a look at the examples below which show how multi-byte characters can affect the results of both functions:

SELECT LENGTH('€'); -- gives 3, it is a 3 byte string
SELECT CHAR_LENGTH('€'); -- gives 1, it is a 1 character string
 
SELECT LENGTH('漢字仮名交じり文©'); -- gives 26, a 26 byte string
SELECT CHAR_LENGTH('漢字仮名交じり文©'); -- gives 9, a 9 character string

The preceding examples show the large differences which can come from passing the same string to the different functions.

Even if your application is not expected to handle multi-byte characters, you should use the particular function for the particular measurement you want. It is possible that you will need to support other character sets in the future and proper coding from now will remove much headache down the road.

Something as simple as the © (copyright) symbol may be enough to throw off your database if you use these functions carelessly.

We hope the above tutorial on calculating MySQL string length was helpful.