LENGTH and CHAR_LENGTH in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LENGTH function do in SQL?

LENGTH returns the number of bytes used by a string. It counts the storage size, which may differ from the number of characters if the string uses multi-byte characters.

intermediate

What is the difference between LENGTH and CHAR_LENGTH in SQL?

LENGTH counts bytes, while CHAR_LENGTH counts the number of characters in a string. For single-byte characters, they are the same, but for multi-byte characters, CHAR_LENGTH shows the actual character count.

intermediate

How does CHAR_LENGTH handle multi-byte characters?

CHAR_LENGTH counts each character once, regardless of how many bytes it uses. So, a character like 'é' counts as 1 character even if it uses multiple bytes.

beginner

Write a SQL query to find the number of characters in the column 'name' from the table 'users'.

SELECT CHAR_LENGTH(name) AS character_count FROM users;

beginner

If a string contains only ASCII characters, what will LENGTH and CHAR_LENGTH return?

Both LENGTH and CHAR_LENGTH will return the same number because ASCII characters use one byte each.

What does LENGTH('hello') return in SQL?

A Depends on the character encoding

B 6

C 4

D 5

The string 'hello' has 5 characters and each ASCII character is 1 byte, so LENGTH returns 5.

Which function counts the number of characters, not bytes, in a string?

A LENGTH

B CHAR_LENGTH

C SUBSTRING

D ASCII

CHAR_LENGTH counts characters, while LENGTH counts bytes.

If a string contains the character 'é' (which uses 2 bytes), what will LENGTH('é') return?

A 2

B 1

C 0

D Depends on the database

LENGTH counts bytes, so it returns 2 for 'é' if it uses 2 bytes.

What will CHAR_LENGTH('é') return?

A 1

B 2

C 0

D Depends on the database

CHAR_LENGTH counts characters, so it returns 1 for 'é' regardless of byte size.

Which SQL function would you use to get the byte size of a string?

A CHAR_LENGTH

B COUNT

C LENGTH

D SUBSTRING

LENGTH returns the number of bytes used by the string.

Explain the difference between LENGTH and CHAR_LENGTH in SQL with an example.

Think about how characters like 'é' are stored.

How would you find the number of characters in a text column that may contain multi-byte characters?

Focus on counting characters, not bytes.