LENGTH and CHAR_LENGTH in SQL - Time & Space Complexity

Time Complexity: LENGTH and CHAR_LENGTH

O(n * m)

Understanding Time Complexity

We want to understand how the time to calculate string lengths grows as the input data grows.

How does the work change when we measure length of many strings?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT LENGTH(name), CHAR_LENGTH(name)
FROM employees;

This query calculates the byte length and character length of each employee's name.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Calculating length of each string in the column.
• How many times: Once for each row in the employees table.

How Execution Grows With Input

Each string length calculation takes time proportional to the string's size. Doing this for many rows adds up.

Input Size (n rows)	Approx. Operations
10	Length calculations for 10 strings
100	Length calculations for 100 strings
1000	Length calculations for 1000 strings

Pattern observation: The total work grows linearly with the number of rows.

Final Time Complexity

Time Complexity: O(n * m)

This means the time grows directly in proportion to the number of rows processed and the average length of the strings.

Common Mistake

[X] Wrong: "Calculating LENGTH or CHAR_LENGTH is instant and does not depend on string size or number of rows."

[OK] Correct: Each length calculation must look at the string characters, so longer strings or more rows take more time.

Interview Connect

Understanding how simple functions like LENGTH scale helps you reason about query performance and data processing in real projects.

Self-Check

"What if we added a WHERE clause to filter rows before calculating LENGTH? How would the time complexity change?"

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