MySQL CHAR_LENGTH() – How to find the number of characters in a string?

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 CHAR_LENGTH() function.

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 SELECT Statement.

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 LENGTH() and 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,

Conclusion

CHAR_LENGTH() calculates the number of characters in a string. The value remains the same irrespective of the character set of the string.