In this tutorial, we will study the MySQL
CHAR_LENGTH() function. The
CHAR_LENGTH() function is used to return the length of the string as the number of characters in it. The
CHARACTER_LENGTH() function is the same as
Both of them do the same thing i.e., returning the number of characters in a string. For instance, if we have a string with the value “HELLO”, the
CHAR_LENGTH() function would return 5, irrespective of the character set it belongs to.
CHAR_LENGTH() is usually used with the
Syntax for MySQL CHAR_LENGTH()
Depending on which function name you use, the syntax is –
CHAR_LENGTH(string); CHARACTER_LENGTH(string);Code language: SQL (Structured Query Language) (sql)
Where string is an argument whose number of characters is to be found.
Examples of MySQL CHAR_LENGTH()
Let’s take a look at some of the examples of the MySQL char_length() function.
1. Basic Examples
Let us find the lengths of the following expressions – 456, “Hello World!”, “JournalDev123” and NULL. The queries are –
SELECT CHAR_LENGTH(456); SELECT CHAR_LENGTH("Hello World!"); SELECT CHARACTER_LENGTH("JournalDev123") AS Length; SELECT CHARACTER_LENGTH(NULL);Code language: SQL (Structured Query Language) (sql)
Here, for the last two examples, we have used
CHARACTER_LENGTH() instead of
CHAR_LENGTH() to show that both are the same. We can also use MySQL Aliases to get a readable output.
So our output is,
2. Difference between LENGTH() and CHAR_LENGTH()
The main difference between
CHAR_LENGTH() functions is that MySQL
LENGTH() returns the length of the string in bytes whereas
CHAR_LENGTH() returns the length as the count of the characters in it.
Irrespective of the character set,
CHAR_LENGTH() returns the same length for a string. Let us look at the below queries.
SELECT CHAR_LENGTH(CONVERT("@" USING utf32)); SELECT LENGTH(CONVERT("@" USING utf32));Code language: SQL (Structured Query Language) (sql)
Over here, we are using the
CONVERT() function to convert the character set of the string to UTF-32 encoding. UTF-16 is an encoding for the Unicode character set using two or four bytes per character. Now let us see the output for both of them.
As you can see,
CHAR_LENGTH() returns 1, since there is only one character in the string. However, since the character set has been changed, the
LENGTH() function returns 4 as the result. The result of the
LENGTH() function is in terms of bytes of the string.
3. CHAR_LENGTH() with Column Values
Consider the below Employee table.
Let us find the length of the unique values in the Department column. We will use the
DISTINCT keyword and aliases for the query.
SELECT DISTINCT Department, CHAR_LENGTH(Department) AS No_Of_Characters FROM Employee;Code language: SQL (Structured Query Language) (sql)
And we get the output as,
CHAR_LENGTH() calculates the number of characters in a string. The value remains the same irrespective of the character set of the string.