MySQL CHAR_LENGTH() Function

Summary: in this tutorial, you will learn how to use the MySQL CHAR_LENGTH() function to get the number of characters in a string.

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:

+---------------------------------------------+-------------------+
| productName                                 | descriptionLength |
+---------------------------------------------+-------------------+
| 1928 British Royal Navy Airplane            |                28 |
| P-51-D Mustang                              |                45 |
| 1903 Ford Model A                           |                48 |
| 1904 Buick Runabout                         |                48 |
| 1930 Buick Marquette Phaeton                |                48 |
| 1999 Indy 500 Monte Carlo SS                |                54 |
| 1970 Triumph Spitfire                       |                57 |
| 1957 Chevy Pickup                           |                60 |
| F/A 18 Hornet 1/72                          |                60 |
| Boeing X-32A JSF                            |                60 |
| 1957 Vespa GS150                            |                64 |
| 1982 Ducati 996 R                           |                64 |
...Code language: SQL (Structured Query Language) (sql)

Summary

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

Was this tutorial helpful?