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.