LENGTH and CHAR_LENGTH in SQL - Deep Dive

Overview - LENGTH and CHAR_LENGTH

What is it?

LENGTH and CHAR_LENGTH are functions in SQL used to find the size of text data. LENGTH returns the number of bytes used by a string, while CHAR_LENGTH returns the number of characters in the string. These functions help understand how much space text takes or how many characters it contains, which can differ especially with special or multi-byte characters.

Why it matters

Knowing the difference between LENGTH and CHAR_LENGTH helps avoid mistakes when working with text data, especially in languages with special characters or emojis. Without these functions, you might miscount characters or storage needs, leading to errors in data storage, display, or processing. This can cause bugs in applications, wasted storage, or wrong user experiences.

Where it fits

Before learning LENGTH and CHAR_LENGTH, you should understand basic SQL queries and string data types. After mastering these functions, you can explore more advanced string functions like SUBSTRING, TRIM, and COLLATION handling for international text.

Mental Model

Core Idea

LENGTH counts bytes used by a string, while CHAR_LENGTH counts the actual characters, which may be fewer if characters use multiple bytes.

Think of it like...

Imagine a book where LENGTH counts the total number of letters including spaces and punctuation marks, but CHAR_LENGTH counts the number of words. Some words might be long or short, just like characters can take more or fewer bytes.

String: "café"

Bytes: c(1) a(1) f(1) é(2)  → LENGTH = 5 bytes
Characters: c a f é       → CHAR_LENGTH = 4 characters

┌─────────────┐
│   String    │
│  "café"   │
└─────────────┘
     │
     ├─ LENGTH (bytes) → 5
     └─ CHAR_LENGTH (characters) → 4

Build-Up - 6 Steps

1

Foundation

Understanding Basic String Length

Concept: Learn what LENGTH function does in SQL.

The LENGTH function returns the number of bytes used by a string. For simple ASCII text, each character is one byte, so LENGTH equals the number of characters. Example: SELECT LENGTH('hello'); This returns 5 because 'hello' has 5 characters, each one byte.

Result

5

Understanding LENGTH as byte count helps you realize that for simple text, LENGTH and character count are the same.

2

Foundation

Introducing CHAR_LENGTH Function

Concept: Learn what CHAR_LENGTH function does in SQL.

CHAR_LENGTH returns the number of characters in a string, regardless of how many bytes each character uses. For ASCII text, CHAR_LENGTH equals LENGTH. Example: SELECT CHAR_LENGTH('hello'); This returns 5, same as LENGTH for simple text.

Result

5

Knowing CHAR_LENGTH counts characters prepares you for cases where characters use multiple bytes.

3

Intermediate

Difference with Multi-byte Characters

🤔Before reading on: do you think LENGTH and CHAR_LENGTH return the same value for 'café'? Commit to your answer.

Concept: Understand how multi-byte characters affect LENGTH and CHAR_LENGTH differently.

The word 'café' has a special character 'é' which uses 2 bytes in UTF-8 encoding. LENGTH counts bytes, so: SELECT LENGTH('café'); -- returns 5 SELECT CHAR_LENGTH('café'); -- returns 4 Because 'é' is one character but two bytes.

Result

LENGTH = 5, CHAR_LENGTH = 4

Understanding this difference prevents bugs when counting characters in languages with accents or emojis.

4

Intermediate

Using LENGTH and CHAR_LENGTH in Queries

🤔Before reading on: if you want to limit user input by characters, should you use LENGTH or CHAR_LENGTH? Commit to your answer.

Concept: Learn when to use LENGTH vs CHAR_LENGTH in practical SQL queries.

If you want to limit input by visible characters, use CHAR_LENGTH. If you want to limit storage size in bytes, use LENGTH. Example: -- Limit input to 10 characters SELECT * FROM users WHERE CHAR_LENGTH(username) <= 10; -- Check storage size limit SELECT * FROM users WHERE LENGTH(username) <= 20;

Result

Queries filter rows based on character count or byte size accordingly.

Knowing which function fits your goal avoids errors in data validation or storage.

5

Advanced

Impact of Character Encoding on LENGTH

🤔Before reading on: does LENGTH always equal CHAR_LENGTH regardless of encoding? Commit to your answer.

Concept: Explore how different character encodings affect LENGTH results.

LENGTH counts bytes, so its value depends on the string's encoding. For UTF-8, some characters use multiple bytes. For latin1 encoding, each character is one byte. Example: -- UTF-8 encoding SELECT LENGTH('ñ'); -- returns 2 SELECT CHAR_LENGTH('ñ'); -- returns 1 -- latin1 encoding SELECT LENGTH(CONVERT('ñ' USING latin1)); -- returns 1 SELECT CHAR_LENGTH(CONVERT('ñ' USING latin1)); -- returns 1

Result

LENGTH varies with encoding; CHAR_LENGTH counts characters consistently.

Understanding encoding effects helps avoid miscounting bytes or characters in internationalized applications.

6

Expert

Surprising Behavior with Trailing Spaces

🤔Before reading on: do LENGTH and CHAR_LENGTH count trailing spaces the same way? Commit to your answer.

Concept: Discover how trailing spaces affect LENGTH and CHAR_LENGTH differently in some SQL dialects.

In some databases, trailing spaces may be ignored or counted differently. For example, in MySQL: SELECT LENGTH('abc '), CHAR_LENGTH('abc '); Both return 4 because spaces count as characters and bytes. But in some cases, trailing spaces in CHAR fields are trimmed, affecting LENGTH but not CHAR_LENGTH. This behavior depends on data type and database settings.

Result

Trailing spaces may cause LENGTH and CHAR_LENGTH to differ unexpectedly.

Knowing this prevents subtle bugs when comparing string lengths or validating input with spaces.

Under the Hood

LENGTH counts the total bytes used by the string in its stored encoding, including multi-byte characters. CHAR_LENGTH counts the number of characters by decoding the string according to its character set and counting each character once, regardless of byte size. Internally, the database uses encoding rules to interpret bytes as characters.

Why designed this way?

These functions exist because storage size (bytes) and logical content size (characters) are different concepts. Early databases stored text in single-byte encodings, so LENGTH and CHAR_LENGTH were the same. With globalization and multi-byte encodings like UTF-8, distinguishing byte size and character count became necessary to handle storage and display correctly.

┌───────────────┐
│   Input Text  │
│  "café"     │
└──────┬────────┘
       │
       ▼
┌───────────────┐          ┌───────────────┐
│  Encoding     │─────────▶│  Byte Array   │
│  (UTF-8)     │          │ c a f é (5B)  │
└───────────────┘          └─────┬─────────┘
                                    │
                   ┌────────────────┴───────────────┐
                   │                                │
           ┌───────▼───────┐                ┌───────▼────────┐
           │ LENGTH()      │                │ CHAR_LENGTH()  │
           │ Counts bytes  │                │ Counts chars   │
           │ Returns 5     │                │ Returns 4      │
           └──────────────┘                └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does LENGTH always equal CHAR_LENGTH for any string? Commit yes or no.

Common Belief: LENGTH and CHAR_LENGTH always return the same number because they both measure string length.

Reality: LENGTH counts bytes, CHAR_LENGTH counts characters. For multi-byte characters, LENGTH is larger than CHAR_LENGTH.

Why it matters: Assuming they are equal causes bugs in text processing, especially with international characters or emojis.

Quick: If a string has trailing spaces, do LENGTH and CHAR_LENGTH ignore them? Commit yes or no.

Common Belief: Trailing spaces are ignored by LENGTH and CHAR_LENGTH functions.

Reality: Trailing spaces count as characters and bytes, so they affect both LENGTH and CHAR_LENGTH unless the database trims them automatically.

Why it matters: Ignoring trailing spaces can cause wrong length calculations and data validation errors.

Quick: Does the character encoding affect LENGTH results? Commit yes or no.

Common Belief: LENGTH returns the same value regardless of character encoding.

Reality: LENGTH depends on encoding because it counts bytes. Multi-byte encodings increase LENGTH compared to single-byte encodings.

Why it matters: Ignoring encoding effects leads to wrong storage size estimates and potential data truncation.

Quick: Can CHAR_LENGTH return a larger number than LENGTH? Commit yes or no.

Common Belief: CHAR_LENGTH can be larger than LENGTH if the string has special characters.

Reality: CHAR_LENGTH is never larger than LENGTH because each character uses at least one byte; multi-byte characters make LENGTH larger or equal.

Why it matters: Misunderstanding this can cause confusion in string length logic and validation.

Expert Zone

1

Some databases treat trailing spaces differently in CHAR and VARCHAR types, affecting LENGTH and CHAR_LENGTH results subtly.

2

In collations with combining characters, CHAR_LENGTH counts base characters plus combining marks as separate characters, which may differ from user perception.

3

Using LENGTH on binary data stored as strings can mislead because it counts bytes without character context.

When NOT to use

Avoid LENGTH when you need to count user-visible characters in multi-byte or Unicode strings; use CHAR_LENGTH instead. For binary data or fixed byte storage, LENGTH is appropriate. When working with legacy single-byte encodings, LENGTH and CHAR_LENGTH behave similarly, so either can be used.

Production Patterns

In production, CHAR_LENGTH is used to validate user input length in characters to prevent cutting multi-byte characters. LENGTH is used to monitor storage size and optimize database space. Both functions are combined in queries to handle internationalization and storage constraints correctly.

Connections

Unicode Encoding

LENGTH and CHAR_LENGTH depend on Unicode encoding schemes like UTF-8.

Understanding Unicode encoding clarifies why byte count and character count differ, improving handling of international text.

Data Storage Optimization

LENGTH helps estimate storage space needed for text fields.

Knowing byte size guides database schema design and storage optimization.

Human Perception of Text

CHAR_LENGTH aligns with how humans count characters, not bytes.

This connection helps design user-friendly input validation and display logic.

Common Pitfalls

#1 Counting characters with LENGTH on multi-byte strings.

Wrong approach:

SELECT LENGTH('😊'); -- returns 4 (bytes), not 1 character

Correct approach:

SELECT CHAR_LENGTH('😊'); -- returns 1 character

Root cause:

Misunderstanding that LENGTH counts bytes, not characters, especially for emojis or special symbols.

#2 Using LENGTH to limit user input length by characters.

Wrong approach:

WHERE LENGTH(username) <= 10; -- may cut multi-byte characters incorrectly

Correct approach:

WHERE CHAR_LENGTH(username) <= 10; -- limits by actual characters

Root cause:

Confusing byte size with character count leads to improper input validation.

#3 Assuming trailing spaces do not affect length calculations.

Wrong approach:

SELECT LENGTH('abc '); -- expecting 3, but returns 4

Correct approach:

SELECT LENGTH('abc '); -- returns 4, counting space

Root cause:

Not realizing spaces are characters and bytes, affecting length.

Key Takeaways

LENGTH counts the number of bytes used by a string, which can be more than characters for multi-byte encodings.

CHAR_LENGTH counts the number of characters, matching user-visible length regardless of byte size.

Multi-byte characters like accented letters or emojis cause LENGTH and CHAR_LENGTH to differ.

Choosing between LENGTH and CHAR_LENGTH depends on whether you care about storage size or character count.

Understanding encoding and trailing spaces is essential to avoid bugs in string length calculations.

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