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 SELECT Statement.
Syntax for MySQL LENGTH()
LENGTH(string)Code language: SQL (Structured Query Language) (sql)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);Code language: SQL (Structured Query Language) (sql)And we get our output as,
As you can see, the length of NULL is returned as NULL by the LENGTH() function.
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 Length_Of_Names.
SELECT Name, LENGTH(Name) AS Length_Of_Names FROM Employee;Code language: SQL (Structured Query Language) (sql)And we get our output as,
3. Length of Character Sets
The 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:
SELECT LENGTH(“@”);Code language: SQL (Structured Query Language) (sql)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));Code language: SQL (Structured Query Language) (sql)The 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;Code language: SQL (Structured Query Language) (sql)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.
Conclusion
The MySQL 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.
References
- MySQL Official documentation on
LENGTH()function. - MySQL Official documentation on character sets.