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.