LENGTH and CHAR_LENGTH in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Difference between LENGTH and CHAR_LENGTH with multibyte characters

Consider the string 'café' stored in a UTF-8 encoded MySQL database. What is the output of the following query?

SELECT LENGTH('café') AS length_bytes, CHAR_LENGTH('café') AS length_chars;

MySQL

SELECT LENGTH('café') AS length_bytes, CHAR_LENGTH('café') AS length_chars;

A length_bytes = 4, length_chars = 4

B length_bytes = 5, length_chars = 4

C length_bytes = 4, length_chars = 5

D length_bytes = 5, length_chars = 5

💡 Hint

Remember that LENGTH counts bytes, and CHAR_LENGTH counts characters.

The character 'é' in UTF-8 uses 2 bytes. So 'café' has 4 characters but 5 bytes total.

❓ query_result

intermediate

Using LENGTH and CHAR_LENGTH on ASCII string

What is the output of this query?

SELECT LENGTH('hello') AS length_bytes, CHAR_LENGTH('hello') AS length_chars;

MySQL

SELECT LENGTH('hello') AS length_bytes, CHAR_LENGTH('hello') AS length_chars;

A length_bytes = 5, length_chars = 4

B length_bytes = 4, length_chars = 5

C length_bytes = 6, length_chars = 5

D length_bytes = 5, length_chars = 5

💡 Hint

ASCII characters use 1 byte each.

Each ASCII character is 1 byte, so both LENGTH and CHAR_LENGTH return 5.

📝 Syntax

advanced

Identify the syntax error in LENGTH usage

Which option contains a syntax error when using LENGTH or CHAR_LENGTH functions in MySQL?

A SELECT LENGTH 'test';

B SELECT CHAR_LENGTH('test') FROM dual;

C SELECT LENGTH('test');

D SELECT LENGTH('test') FROM dual;

💡 Hint

Check the parentheses usage in function calls.

MySQL requires parentheses around function arguments. Option A misses parentheses causing syntax error.

🧠 Conceptual

advanced

Why use CHAR_LENGTH instead of LENGTH for user input?

Why is it better to use CHAR_LENGTH instead of LENGTH when validating the length of user input strings in a UTF-8 encoded MySQL database?

A Because CHAR_LENGTH counts bytes, which is more accurate for storage size.

B Because LENGTH counts characters, not bytes, so it is faster.

C Because CHAR_LENGTH counts characters, not bytes, so it reflects the actual number of characters the user typed.

D Because LENGTH counts words, which is useful for input validation.

💡 Hint

Think about multibyte characters and user experience.

CHAR_LENGTH counts characters, so it matches what users expect when counting input length, especially with multibyte characters.

❓ query_result

expert

Output of LENGTH and CHAR_LENGTH on emoji string

What is the output of this query?

SELECT LENGTH('👍👍') AS length_bytes, CHAR_LENGTH('👍👍') AS length_chars;

Assume the database uses UTF-8 encoding.

MySQL

SELECT LENGTH('👍👍') AS length_bytes, CHAR_LENGTH('👍👍') AS length_chars;

A length_bytes = 8, length_chars = 2

B length_bytes = 4, length_chars = 2

C length_bytes = 2, length_chars = 2

D length_bytes = 8, length_chars = 4

💡 Hint

Emoji characters usually use 4 bytes each in UTF-8.

Each '👍' emoji is 4 bytes in UTF-8, so LENGTH returns 8 bytes total, CHAR_LENGTH counts 2 characters.