LENGTH and CHAR_LENGTH in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to find the length of the string 'Hello'.

SQL

SELECT [1]('Hello') AS length_value;

Drag options to blanks, or click blank then click option'

ALENGTH

BCOUNT

CSIZE

DLEN

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT which counts rows, not string length.

Using LEN which is not standard SQL.

Using SIZE which is not a string function.

The LENGTH function returns the number of bytes in the string. It is commonly used to find string length in SQL.

2fill in blank

medium

Complete the code to find the number of characters in the string 'café'.

SQL

SELECT [1]('café') AS char_count;

Drag options to blanks, or click blank then click option'

ACHAR_LENGTH

BSIZE

CCOUNT

DLENGTH

Attempts:

3 left

💡 Hint

Common Mistakes

Using LENGTH which counts bytes, not characters.

Using COUNT which is for rows.

Using SIZE which is not a string function.

CHAR_LENGTH returns the number of characters in a string, which is important for multibyte characters like 'é'.

3fill in blank

hard

Fix the error in the code to correctly get the character length of the string 'naïve'.

SQL

SELECT [1]('naïve') AS length;

Drag options to blanks, or click blank then click option'

ALEN

BCHAR_LENGTH

CLENGTH

DCOUNT

Attempts:

3 left

💡 Hint

Common Mistakes

Using LENGTH which counts bytes, not characters.

Using LEN which is not standard SQL.

Using COUNT which counts rows.

CHAR_LENGTH correctly counts characters including multibyte ones like 'ï'. LENGTH counts bytes which can be misleading.

4fill in blank

hard

Fill both blanks to create a query that shows both byte length and character length of 'résumé'.

SQL

SELECT [1]('résumé') AS byte_length, [2]('résumé') AS char_length;

Drag options to blanks, or click blank then click option'

ALENGTH

BCHAR_LENGTH

CLEN

DCOUNT

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEN which is not standard SQL.

Using COUNT which counts rows.

Mixing up LENGTH and CHAR_LENGTH.

LENGTH returns the byte length, while CHAR_LENGTH returns the character count, useful for strings with multibyte characters.

5fill in blank

hard

Fill all three blanks to create a query that selects the string, its byte length, and its character length from a table named words.

SQL

SELECT word, [1](word) AS byte_len, [2](word) AS char_len FROM words WHERE [3](word) > 5;

Drag options to blanks, or click blank then click option'

ALENGTH

BCHAR_LENGTH

DCOUNT

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT in WHERE clause which counts rows, not string length.

Using LEN which is not standard SQL.

Mixing up LENGTH and CHAR_LENGTH in the wrong places.

The query selects each word, its byte length using LENGTH, its character length using CHAR_LENGTH, and filters words with byte length greater than 5.

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