LENGTH and CHAR_LENGTH in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LENGTH function do in SQL?

LENGTH returns the number of bytes used by a string. It counts the storage size, which may differ from the number of characters if the string uses multi-byte characters.

intermediate

What is the difference between LENGTH and CHAR_LENGTH in SQL?

LENGTH counts bytes, while CHAR_LENGTH counts the number of characters in a string. For single-byte characters, they are the same, but for multi-byte characters, CHAR_LENGTH shows the actual character count.

intermediate

How does CHAR_LENGTH handle multi-byte characters?

CHAR_LENGTH counts each character once, regardless of how many bytes it uses. So, a character like 'é' counts as 1 character even if it uses multiple bytes.

beginner

Write a SQL query to find the number of characters in the column 'name' from the table 'users'.

SELECT CHAR_LENGTH(name) AS character_count FROM users;

beginner

If a string contains only ASCII characters, what will LENGTH and CHAR_LENGTH return?

Both LENGTH and CHAR_LENGTH will return the same number because ASCII characters use one byte each.

What does LENGTH('hello') return in SQL?

A Depends on the character encoding

B 6

C 4

D 5

The string 'hello' has 5 characters and each ASCII character is 1 byte, so LENGTH returns 5.

Which function counts the number of characters, not bytes, in a string?

A LENGTH

B CHAR_LENGTH

C SUBSTRING

D ASCII

CHAR_LENGTH counts characters, while LENGTH counts bytes.

If a string contains the character 'é' (which uses 2 bytes), what will LENGTH('é') return?

A 2

B 1

C 0

D Depends on the database

LENGTH counts bytes, so it returns 2 for 'é' if it uses 2 bytes.

What will CHAR_LENGTH('é') return?

A 1

B 2

C 0

D Depends on the database

CHAR_LENGTH counts characters, so it returns 1 for 'é' regardless of byte size.

Which SQL function would you use to get the byte size of a string?

A CHAR_LENGTH

B COUNT

C LENGTH

D SUBSTRING

LENGTH returns the number of bytes used by the string.

Explain the difference between LENGTH and CHAR_LENGTH in SQL with an example.

Think about how characters like 'é' are stored.

How would you find the number of characters in a text column that may contain multi-byte characters?

Focus on counting characters, not bytes.

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