A Complete Guide to the MySQL CHARACTER_LENGTH() Function

When working with text data in MySQL, understanding how to measure string lengths is essential. The CHARACTER_LENGTH() function (or its synonym, CHAR_LENGTH()) helps you determine the number of characters in a string, making it invaluable for data validation, truncation checks, and text processing.

Unlike LENGTH(), which returns the byte count of a string (affected by character encoding), CHARACTER_LENGTH() strictly counts characters—ideal for multibyte encodings like UTF-8. Let’s explore how this function works and how you can apply it in real-world scenarios.

Understanding the Basics of CHARACTER_LENGTH()

The CHARACTER_LENGTH() function takes a string as input and returns the number of characters it contains. Its syntax is straightforward:

CHARACTER_LENGTH(string)

For example:

SELECT CHARACTER_LENGTH('MySQL') AS char_count;

This query returns 5 because “MySQL” consists of five characters.

A key distinction is that CHARACTER_LENGTH() handles multibyte characters correctly. Compare it with LENGTH():

SELECT
    CHARACTER_LENGTH(' café ') AS char_count,
    LENGTH(' café ') AS byte_count;

Here, CHARACTER_LENGTH() returns 5 (counting the space, ‘c’, ‘a’, ‘f’, and ‘é’), while LENGTH() may return a higher number if ‘é’ occupies multiple bytes.

Working with Column Data

A common use case is checking the length of text stored in database columns. Suppose you have a users table with a username column, and you want to enforce a character limit:

SELECT
    username,
    CHARACTER_LENGTH(username) AS name_length
FROM users
WHERE CHARACTER_LENGTH(username) > 15;

This query identifies usernames exceeding 15 characters, helping you maintain consistency in your data.

Handling Multibyte Characters

If your application deals with multilingual text (e.g., Chinese, Japanese, or emojis), CHARACTER_LENGTH() ensures accurate counting:

SELECT CHARACTER_LENGTH('数据库') AS chinese_chars;

This returns 3, correctly counting each Chinese character, whereas LENGTH() might return a higher value depending on encoding.

Combining with Other String Functions

You can pair CHARACTER_LENGTH() with functions like CONCAT(), SUBSTRING(), or TRIM() for advanced text processing. For instance, truncating a string while preserving whole words:

SELECT
    description,
    CONCAT(SUBSTRING(description, 1, 10), '...') AS truncated,
    CHARACTER_LENGTH(description) AS original_length
FROM products;

This shortens product descriptions to 10 characters while appending an ellipsis.

Practical Applications

• Input Validation: Ensure form fields (e.g., tweets, comments) adhere to character limits.
• Data Cleaning: Identify unusually long or short entries in text columns.
• Localization: Count characters correctly in multilingual applications without byte-counting side effects.

Conclusion

The CHARACTER_LENGTH() function is a reliable tool for measuring string lengths in MySQL, especially when dealing with multibyte characters. Unlike LENGTH(), it focuses on character count rather than bytes, making it essential for global applications.

Whether you’re validating input, analyzing text data, or handling multilingual content, CHARACTER_LENGTH() ensures accuracy. Next time you work with strings in MySQL, consider whether you need byte length or character length—and choose the right function for the job.