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.