CHAR_LENGTH

Introduction to the MySQL CHAR_LENGTH function

The CHAR_LENGTH() function returns the number of characters in a string regardless of character set used.

Here’s the basic syntax of the CHAR_LENGTH() function:

CHAR_LENGTH(string)Code language: SQL (Structured Query Language) (sql)

In this syntax, the string is the input string which you want to calculate the character length.

If the string is NULL, the CHAR_LENGTH() function returns NULL.

Unlike the LENGTH() function that returns the length of a string in bytes, the CHAR_LENGTH() returns the length of the string in characters.

Therefore, the CHAR_LENGTH() function can be useful when you work with multibyte character sets like UTF-8.

MySQL CHAR_LENGTH function examples

Let’s take some examples of using the CHAR_LENGTH() function.

1) Using CHAR_LENGTH with single-byte character set string example

A single-byte character set represents each character by a single byte. Therefore, the CHAR_LENGTH() function will return the same result as the LENGTH() function, as each byte represents one character:

SELECT
  CHAR_LENGTH('Hello') AS character_count;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+
| character_count |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using CHAR_LENGTH with multibyte character set string example

A multibyte character set may use multiple bytes to represent each character. However, the CHAR_LENGTH() will count the number characters accurately in such situations. For example:

SELECT
  CHAR_LENGTH('Café') AS character_count;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+
| character_count |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The query returns 4 because the string Café contains four characters. Even though the 'é' character is represented by two bytes in UTF-8, the CHAR_LENGTH() function counts it as a single character.

3) Using CHAR_LENGTH function with table data example

We’ll use the products table from the sample database:

The following example uses the CHAR_LENGTH() function to return the character counts of the product descriptions:

SELECT
  productName,
  CHAR_LENGTH(productDescription) descriptionLength
FROM
  products
ORDER BY
  descriptionLength;Code language: SQL (Structured Query Language) (sql)

Output:

Summary

• Use MySQL CHAR_LENGTH() function to count the number of characters in a string, regardless of the character set is currently being used.