In this tutorial, we will study the MySQL
LENGTH() function. While working with tables in MySQL, there may be a situation in which you may want to calculate the length of a value in a column.
For this purpose, MySQL provides us with the
LENGTH() function. The MySQL
LENGTH() function returns the length of a given string in bytes. So, if we use
LENGTH() on five 3-byte characters, we get 15 as our result (5 x 3 = 15). The
LENGTH() function is mostly used with the
Syntax for MySQL LENGTH()
Where string is the argument whose length is to be found out.
Examples of MySQL LENGTH()
Let’s take a look at some of the examples of the MySQL LENGTH function.
1. Basic Examples
Let us find the length of the following strings using the
LENGTH() function – “JournalDev”, 123456789, “dev@” and NULL. The query is,
SELECT LENGTH(“JournalDev”); SELECT LENGTH(123456789); SELECT LENGTH(“dev@”); SELECT LENGTH(NULL);
And we get our output as,
As you can see, the length of NULL is returned as NULL by the
2. Using MySQL LENGTH() On Column Values
Consider the below Employee table.
Let us write a query which will return the names and their lengths under an alias
SELECT Name, LENGTH(Name) AS Length_Of_Names FROM Employee;
And we get our output as,
3. Length of Character Sets
LENGTH() function does not measure the number of characters in an expression, it measures the number of bytes of that expression. So these numbers of bytes vary on the character set of the expression.
You can read more about character sets here. By default, MySQL uses the Latin character set.
This can be limiting depending on what the application of your database is. For instance, more often than not, the character encoding of the data on the internet is of UTF-8 (also known as Unicode) character set.
This is because UTF-8 supports a variety of languages and scripts. There are many other character sets like UTF-16, UTF-32, and so on. Let us play around with the
LENGTH() function by converting the data to a different character set.
Let us take the following query:
The output, as you’d expect would be,
How about converting this to another format, say UTF-16 and UTF-32? UTF-16 is an encoding for the Unicode character set using two or four bytes per character whereas UTF-32 encoding uses 4 bytes per character.
SELECT LENGTH(CONVERT("@" USING utf16)); SELECT LENGTH(CONVERT("@" USING utf32));
CONVERT function helps us to convert the type/character set of a string. In the above two queries, we have converted the character set to UTF-16 and UTF-32 respectively.
Our output is,
As you can see, UTF-16 uses 2 bytes per character and hence the length of “@” is now 2. Similarly, UTF-32 uses 4 bytes per character and so the length of “@” is returned as 4.
Let us take another example. This time, let us write a query that will return the names and their lengths from the Employee table, after conversion to the UTF-16 character set, under an alias Length_Of_Names_UTF16.
SELECT Name, LENGTH(CONVERT((Name) USING utf16)) AS Length_Of_Names_UTF16 FROM Employee;
And the output you get is,
As you can see, the length has now doubled than what we saw in an earlier example of the same query without converting the Name column to UTF-16.
LENGTH() function returns the length in bytes and not as the count value of the number of characters in an expression. I would recommend you to check the below links to read more about the
LENGTH() function and character sets.