LENGTH and CHAR_LENGTH in SQL - Step-by-Step Execution

Concept Flow - LENGTH and CHAR_LENGTH

Input String

↓

Output: Numeric Value

LENGTH counts bytes in a string, CHAR_LENGTH counts characters. They differ when string has multi-byte characters.

Execution Sample

SQL

SELECT LENGTH('café'), CHAR_LENGTH('café');

This query returns the byte length and character length of the string 'café'.

Execution Table

Step	Function	Input String	Output Value	Explanation
1	LENGTH	'café'	5	Counts bytes: 'é' is 2 bytes, total 5 bytes
2	CHAR_LENGTH	'café'	4	Counts characters: 'c','a','f','é' = 4 characters
3	END			Query finishes after both functions return values

💡 Both functions return their counts; LENGTH counts bytes, CHAR_LENGTH counts characters

Variable Tracker

Variable	Start	After LENGTH	After CHAR_LENGTH	Final
Input String	'café'	'café'	'café'	'café'
LENGTH Result	NULL	5	5	5
CHAR_LENGTH Result	NULL	NULL	4	4

Key Moments - 2 Insights

Why does LENGTH('café') return 5 but CHAR_LENGTH('café') returns 4?

Because LENGTH counts bytes and 'é' uses 2 bytes in UTF-8, while CHAR_LENGTH counts characters, so it counts 'é' as one character (see execution_table rows 1 and 2).

Will LENGTH and CHAR_LENGTH always return the same number?

No, they differ when the string contains multi-byte characters like accented letters or emojis, as shown in the example with 'café'.

Visual Quiz - 3 Questions

Look at the execution table, what is the output of LENGTH('café')?

A 3

B 4

C 5

D 6

💡 Hint

Check the row where Function is LENGTH and Input String is 'café' in the execution_table.

At which step does CHAR_LENGTH return its value?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look for the row where Function is CHAR_LENGTH in the execution_table.

If the input string was 'cat' (all ASCII), how would LENGTH and CHAR_LENGTH compare?

A LENGTH would be greater than CHAR_LENGTH

B Both would be equal

C CHAR_LENGTH would be greater than LENGTH

D Both would be zero

💡 Hint

ASCII characters use 1 byte each, so LENGTH and CHAR_LENGTH count the same number.

Concept Snapshot

LENGTH(string) returns the number of bytes in the string.
CHAR_LENGTH(string) returns the number of characters.
They differ when string has multi-byte characters.
Use LENGTH for byte size, CHAR_LENGTH for character count.
Example: LENGTH('café')=5, CHAR_LENGTH('café')=4.

Full Transcript

This lesson shows how LENGTH and CHAR_LENGTH work in SQL. LENGTH counts bytes in a string, so multi-byte characters like 'é' count as more than one. CHAR_LENGTH counts characters, so 'é' counts as one. For example, 'café' has 5 bytes but 4 characters. The execution table shows LENGTH returns 5 and CHAR_LENGTH returns 4 for 'café'. This difference is important when working with strings containing special characters. ASCII strings have equal LENGTH and CHAR_LENGTH. Remember: LENGTH is byte count, CHAR_LENGTH is character count.

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