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.

Practice

1. What does the MySQL function LENGTH() return when used on a string?

easy

A. The number of words in the string

B. The number of characters in the string

C. The number of lines in the string

D. The number of bytes in the string

Solution

1. Step 1: Understand LENGTH() function

   The LENGTH() function returns the number of bytes used by the string, not characters.

2. Step 2: Compare with CHAR_LENGTH()

   CHAR_LENGTH() counts characters, LENGTH() counts bytes, which can differ for multibyte characters.

3. Final Answer:

   The number of bytes in the string -> Option D

4. Quick Check:

   LENGTH() = bytes count [OK]

Hint: LENGTH counts bytes, CHAR_LENGTH counts characters [OK]

Common Mistakes:

• Confusing LENGTH() with CHAR_LENGTH()
• Assuming LENGTH() counts characters
• Thinking LENGTH() counts words

2. Which of the following is the correct syntax to get the number of characters in a string column named name in MySQL?

easy

A. SELECT LENGTH(name) FROM users;

B. SELECT CHAR_LENGTH(name) FROM users;

C. SELECT COUNT(name) FROM users;

D. SELECT SIZE(name) FROM users;

Solution

1. Step 1: Identify function for character count

   CHAR_LENGTH() returns the number of characters in a string, which is what we want.

2. Step 2: Check syntax correctness

   SELECT CHAR_LENGTH(name) FROM users; is valid syntax to get character count per row.

3. Final Answer:

   SELECT CHAR_LENGTH(name) FROM users; -> Option B

4. Quick Check:

   CHAR_LENGTH() counts characters [OK]

Hint: Use CHAR_LENGTH() to count characters, not LENGTH() [OK]

Common Mistakes:

• Using LENGTH() instead of CHAR_LENGTH() for characters
• Using COUNT() which counts rows, not string length
• Using non-existent SIZE() function

3. Given a table messages with a column text containing the value 'café', what will the query SELECT LENGTH(text), CHAR_LENGTH(text) FROM messages; return if the encoding is UTF-8?

medium

A. 5, 4

B. 4, 5

C. 4, 4

D. 5, 5

Solution

1. Step 1: Understand UTF-8 encoding for 'café'

   The character 'é' uses 2 bytes in UTF-8, so total bytes = 3 (c,a,f) + 2 (é) = 5 bytes.

2. Step 2: Count characters vs bytes

   CHAR_LENGTH counts characters: 'c','a','f','é' = 4 characters. LENGTH counts bytes = 5.

3. Final Answer:

   5, 4 -> Option A

4. Quick Check:

   LENGTH=5 bytes, CHAR_LENGTH=4 chars [OK]

Hint: UTF-8 chars may use multiple bytes; LENGTH > CHAR_LENGTH [OK]

Common Mistakes:

• Assuming LENGTH and CHAR_LENGTH return same value
• Ignoring multibyte characters in UTF-8
• Swapping LENGTH and CHAR_LENGTH results

4. You wrote the query SELECT LENGTH('hello'), CHAR_LENGTH('hello'); but got an error. What is the likely cause?

medium

A. No error; the query is valid and returns results

B. Missing FROM clause in the query

C. Incorrect function names, LENGTH and CHAR_LENGTH do not exist

D. Using single quotes instead of double quotes

Solution

1. Step 1: Check syntax of the query

   SELECT LENGTH('hello'), CHAR_LENGTH('hello'); is valid in MySQL and does not require FROM clause.

2. Step 2: Verify function existence and quotes

   LENGTH and CHAR_LENGTH are valid functions; single quotes are correct for string literals.

3. Final Answer:

   No error; the query is valid and returns results -> Option A

4. Quick Check:

   Query syntax is valid [OK]

Hint: MySQL allows SELECT without FROM for expressions [OK]

Common Mistakes:

• Thinking FROM clause is mandatory in all SELECT queries
• Confusing string quotes usage
• Assuming LENGTH or CHAR_LENGTH are invalid functions

5. You want to find all rows in a table users where the username column has exactly 5 characters, but some usernames contain multibyte UTF-8 characters. Which query correctly finds these rows?

hard

A. SELECT * FROM users WHERE LENGTH(username) = CHAR_LENGTH(username);

B. SELECT * FROM users WHERE LENGTH(username) = 5;

C. SELECT * FROM users WHERE CHAR_LENGTH(username) = 5;

D. SELECT * FROM users WHERE LENGTH(username) > 5;

Solution

1. Step 1: Understand difference between LENGTH and CHAR_LENGTH

   LENGTH counts bytes, which can be more than characters for multibyte UTF-8 chars.

2. Step 2: Choose function that counts characters

   CHAR_LENGTH counts characters correctly regardless of byte size, so use CHAR_LENGTH(username) = 5.

3. Final Answer:

   SELECT * FROM users WHERE CHAR_LENGTH(username) = 5; -> Option C

4. Quick Check:

   Use CHAR_LENGTH for character count in UTF-8 [OK]

Hint: Use CHAR_LENGTH to count characters, not bytes [OK]

Common Mistakes:

• Using LENGTH() which counts bytes, not characters
• Assuming byte count equals character count
• Using incorrect comparison operators