Using CHAR_LENGTH and LENGTH functions in MySQL 8

Introduction

In MySQL, understanding the nuances of string functions can be vital when working with text data. Two such string functions, CHAR_LENGTH() and LENGTH(), are used to return the length of a string. This tutorial delves into the proper usage of these functions, illustrating their behavior through a series of examples.

The CHAR_LENGTH Function

The CHAR_LENGTH() function returns the number of characters in a string. It is important to note that this function counts characters, not bytes, and it is Unicode-aware — meaning it accurately counts the number of multibyte characters in a string.

Basic Example

SELECT CHAR_LENGTH('Hello, World!') AS CharLength;

Output:

13

This returns the count of characters within our string which includes punctuations and spaces.

Handling Multibyte Characters

SELECT CHAR_LENGTH('Âñòthèr Êxämplé') AS CharLength;

Output:

17

Even though this string contains multibyte characters, CHAR_LENGTH() counts each character as one, regardless of its byte count.

The LENGTH Function

The LENGTH() function, on the other hand, returns the length of a string in bytes. It differs from CHAR_LENGTH() because it is not Unicode-aware and counts bytes — which can yield different results, especially for multibyte character sets.

Basic Example Using ASCII Characters

SELECT LENGTH('Hello, World!') AS ByteLength;

Output:

13

In this example, since we’re using ASCII characters which are single-byte, the number of bytes is equal to the number of characters.

Example With Multibyte Characters

SELECT LENGTH('Âñòthèr Êxämplé') AS ByteLength;

Output:

23

When applied to the same string as before, LENGTH() returns a higher value as it distinguishes multibyte characters and counts their respective bytes.

Comparing CHAR_LENGTH and LENGTH

Let’s compare both functions side by side with a string containing a mix of single and multibyte characters:

SELECT CHAR_LENGTH('Ñośñ'), LENGTH('Ñośñ') AS comparison;

Output:

4, 7

Here, we can see that while the number of characters is four, the number to bytes is seven, showing the fundamental difference between the two functions.

Using CHAR_LENGTH and LENGTH in Table Columns

Not only can you determine the length of literal strings, but you can also use CHAR_LENGTH() and LENGTH() to find the lengths of strings stored in tables.

Example with Database Table

CREATE TABLE sample_texts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(255)
);

INSERT INTO sample_texts(content) VALUES
    ('Hello, World!'),
    ('Âñòthèr Êxämplé'),
    ('Short'),
    ('A significantly longer example text.');

SELECT id, content, CHAR_LENGTH(content) AS CharLength,
    LENGTH(content) AS ByteLength FROM sample_texts;

This script creates a table with some sample texts and then uses a single SELECT query to get the character length and byte length for each text record.

Use Cases in Real-World Scenarios

Here are a few real-world scenarios where understanding the difference between CHAR_LENGTH() and LENGTH() could be crucial:

• Data Validation: Verifying field sizes by character count versus storage size in bytes.
• Text Processing: Understanding the true length of a string for purposes like substring operations or data formatting.
• Handling User Input: Ensuring multibyte characters (like emojis) are considered correctly in character-limited fields.

Advanced Examples: Combining with Other Functions

Both CHAR_LENGTH() and LENGTH() can be used in combination with other MySQL functions to perform more complex operations.

Substring and Length Functions

SELECT content, SUBSTRING(content, 1, CHAR_LENGTH(content) / 2) AS HalfString
FROM sample_texts;

This query returns the first half of each string in the sample_texts table by using the CHAR_LENGTH() function to determine the midpoint of each text.

Ordering by Length of Content

SELECT content FROM sample_texts
ORDER BY CHAR_LENGTH(content) DESC;

This selects all contents from the sample_texts table and orders them by their character length in descending order, from the largest to the smallest strings.

Incorporating conditional logic, you could use these functions to perform specific actions if a string’s length meets certain criteria:

SELECT content,
CASE
    WHEN CHAR_LENGTH(content) <= 10 THEN 'Short content'
    ELSE 'Long content'
END as ContentType
FROM sample_texts;

Conclusion

The CHAR_LENGTH() and LENGTH() functions in MySQL 8 are essential tools for managing text data. Though they serve similar purposes, their distinction makes them uniquely useful within various applications, particularly when dealing with internationalization and multibyte character sets. By leveraging these functions effectively, developers can ensure accurate data handling within their databases.