LENGTH and CHAR_LENGTH in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Difference between LENGTH and CHAR_LENGTH

Given a table words with a column word containing the value 'café', what is the result of these queries?

1. SELECT LENGTH(word) FROM words;
2. SELECT CHAR_LENGTH(word) FROM words;

Assume UTF-8 encoding.

SQL

CREATE TABLE words (word VARCHAR(10));
INSERT INTO words VALUES ('café');

A 1. 4 2. 4

B 1. 4 2. 5

C 1. 5 2. 5

D 1. 5 2. 4

💡 Hint

LENGTH counts bytes, CHAR_LENGTH counts characters.

The character 'é' uses 2 bytes in UTF-8, so LENGTH counts 5 bytes total, while CHAR_LENGTH counts 4 characters.

❓ query_result

intermediate

Using LENGTH and CHAR_LENGTH with ASCII text

What is the output of these queries on a table texts with a single row where text = 'hello'?

SQL

CREATE TABLE texts (text VARCHAR(10));
INSERT INTO texts VALUES ('hello');

A LENGTH(text) = 5, CHAR_LENGTH(text) = 5

B LENGTH(text) = 6, CHAR_LENGTH(text) = 5

C LENGTH(text) = 5, CHAR_LENGTH(text) = 6

D LENGTH(text) = 6, CHAR_LENGTH(text) = 6

💡 Hint

ASCII characters use 1 byte each.

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

📝 Syntax

advanced

Identify the syntax error in LENGTH usage

Which of the following SQL queries will cause a syntax error?

A SELECT LENGTH('test') + 1 FROM dual;

B SELECT LENGTH('test') FROM dual;

C SELECT LENGTH FROM dual;

D SELECT LENGTH('test') AS len FROM dual;

💡 Hint

LENGTH is a function and needs parentheses with an argument.

Option C uses LENGTH without parentheses or argument, causing a syntax error.

❓ optimization

advanced

Optimizing string length checks

You want to find rows in a table messages where the message length is exactly 10 characters. Which query is more efficient and why?

A SELECT * FROM messages WHERE CHAR_LENGTH(message) = 10;

B SELECT * FROM messages WHERE LENGTH(message) = 10;

C SELECT * FROM messages WHERE LENGTH(message) = CHAR_LENGTH(message) AND LENGTH(message) = 10;

D SELECT * FROM messages WHERE LENGTH(message) > 9 AND LENGTH(message) < 11;

💡 Hint

Consider byte vs character length and indexing.

LENGTH is usually faster because it counts bytes directly; CHAR_LENGTH may be slower due to character decoding.

🧠 Conceptual

expert

Understanding LENGTH and CHAR_LENGTH with multi-byte characters

Consider a table texts with a column content containing the string '😊😊'. What are the results of SELECT LENGTH(content), CHAR_LENGTH(content) FROM texts; assuming UTF-8 encoding?

A LENGTH = 8, CHAR_LENGTH = 2

B LENGTH = 2, CHAR_LENGTH = 8

C LENGTH = 4, CHAR_LENGTH = 2

D LENGTH = 2, CHAR_LENGTH = 2

💡 Hint

Each emoji uses 4 bytes in UTF-8.

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

Practice

1. What is the main difference between LENGTH and CHAR_LENGTH functions in SQL?

easy

A. LENGTH counts characters, CHAR_LENGTH counts words.

B. LENGTH counts bytes, CHAR_LENGTH counts characters.

C. LENGTH counts words, CHAR_LENGTH counts bytes.

D. LENGTH and CHAR_LENGTH both count characters the same way.

Solution

1. Step 1: Understand what LENGTH counts

   LENGTH returns the number of bytes used by the string, which can be more than characters if multi-byte characters exist.

2. Step 2: Understand what CHAR_LENGTH counts

   CHAR_LENGTH returns the number of characters, counting multi-byte characters as one each.

3. Final Answer:

   LENGTH counts bytes, CHAR_LENGTH counts characters. -> Option B

4. Quick Check:

   LENGTH ≠ CHAR_LENGTH when multi-byte chars present [OK]

Hint: Remember: bytes ≠ characters for multi-byte text [OK]

Common Mistakes:

• Confusing bytes with characters
• Assuming both functions return the same count
• Thinking CHAR_LENGTH counts words

2. Which of the following is the correct syntax to get the number of characters in the column name using SQL?

easy

A. SELECT CHAR_LENGTH(name) FROM users;

B. SELECT COUNT(name) FROM users;

C. SELECT LENGTH(name) FROM users;

D. SELECT SIZE(name) FROM users;

Solution

1. Step 1: Identify function for character count

   CHAR_LENGTH is the correct function to count characters in a string column.

2. Step 2: Check syntax correctness

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

3. Final Answer:

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

4. Quick Check:

   Use CHAR_LENGTH() for character count [OK]

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

Common Mistakes:

• Using LENGTH() when characters count is needed
• 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 SELECT LENGTH(text), CHAR_LENGTH(text) FROM messages; return?

medium

A. (5, 5)

B. (4, 4)

C. (4, 5)

D. (5, 4)

Solution

1. Step 1: Understand byte vs character count for 'café'

   The letter 'é' is a 2-byte character in UTF-8, so LENGTH counts bytes: 'c'(1) + 'a'(1) + 'f'(1) + 'é'(2) = 5 bytes.

2. Step 2: Count characters with CHAR_LENGTH

   CHAR_LENGTH counts characters, so 'c', 'a', 'f', 'é' = 4 characters.

3. Final Answer:

   (5, 4) -> Option D

4. Quick Check:

   LENGTH = bytes, CHAR_LENGTH = characters [OK]

Hint: Multi-byte chars increase LENGTH but not CHAR_LENGTH [OK]

Common Mistakes:

• Assuming LENGTH and CHAR_LENGTH return same value
• Counting bytes as characters
• Ignoring multi-byte encoding

4. You wrote this query: SELECT CHAR_LENGTH(name) FROM users WHERE LENGTH(name) > 5; but it returns rows even though some names look shorter than 5 characters. What is the likely issue?

medium

A. The LENGTH function counts bytes, so multi-byte characters make the length larger than character count.

B. The CHAR_LENGTH function is not supported in SQL.

C. The query syntax is incorrect; LENGTH cannot be used in WHERE clause.

D. The name column contains NULL values causing the filter to fail.

Solution

1. Step 1: Understand LENGTH in WHERE clause

   LENGTH(name) counts bytes, so names with multi-byte characters may have byte length greater than character count.

2. Step 2: Reason why rows are returned unexpectedly

   If names have fewer than or equal to 5 characters but more than 5 bytes, the condition LENGTH(name) > 5 succeeds, so rows match unexpectedly.

3. Final Answer:

   The LENGTH function counts bytes, so multi-byte characters make the length larger than character count. -> Option A

4. Quick Check:

   LENGTH counts bytes, affects WHERE filters [OK]

Hint: Remember LENGTH counts bytes, affects filtering with multi-byte text [OK]

Common Mistakes:

• Thinking LENGTH counts characters
• Assuming CHAR_LENGTH unsupported
• Believing LENGTH can't be in WHERE clause

5. You want to find all rows in table comments where the text length is exactly 10 characters, including multi-byte characters. Which query is correct?

hard

A. SELECT * FROM comments WHERE LENGTH(text) = CHAR_LENGTH(text);

B. SELECT * FROM comments WHERE LENGTH(text) = 10;

C. SELECT * FROM comments WHERE CHAR_LENGTH(text) = 10;

D. SELECT * FROM comments WHERE LENGTH(text) > 10 AND CHAR_LENGTH(text) < 10;

Solution

1. Step 1: Identify correct function for character count

   To count characters including multi-byte ones, use CHAR_LENGTH.

2. Step 2: Write condition for exact character length

   Use WHERE CHAR_LENGTH(text) = 10 to filter rows with exactly 10 characters.

3. Final Answer:

   SELECT * FROM comments WHERE CHAR_LENGTH(text) = 10; -> Option C

4. Quick Check:

   Use CHAR_LENGTH for character count filters [OK]

Hint: Use CHAR_LENGTH() to filter by character count, not LENGTH() [OK]

Common Mistakes:

• Using LENGTH() which counts bytes, not characters
• Confusing LENGTH and CHAR_LENGTH equality
• Using contradictory conditions in WHERE clause