Length query to get the number of bytes present inside a string

LENGTH() returns bytes. Use CHAR_LENGTH() when you need the number of characters.

Use LENGTH() to return the number of bytes in a string.

SELECT LENGTH('Welcome');

The output is 7

Using whitespace with LENGTH()

SELECT LENGTH('hellow') AS L1, LENGTH('  hellow  ') AS L2;

output

L1	L2
6	10

Student Table

Let us find out the length of name present in our student table.

SELECT id, name, LENGTH(name) FROM `student`

We will get a list of id , name and length like this

1 John Deo  8
2 Max Ruin  8
3 Arnold      6

We can also use length in numeric field like this

SELECT id, name, LENGTH(name),LENGTH(mark) FROM `student`

Output will be 2 for marks less than 100 and more than 9 ( two digits)

The following example finds rows where the converted value has at most one byte

SELECT id, name FROM `student` WHERE LENGTH(mark) <=1

This can include single-digit values as well as empty-looking values, because numeric data is converted to text for LENGTH().

When dealing with multi-byte character encodings like UTF-8, the length might not represent the number of characters accurately. To address this, MySQL provides several functions to handle character lengths more accurately in various situations:

CHAR_LENGTH(str): Returns the length of a string in terms of characters, not bytes. This is especially useful when dealing with multi-byte character sets like UTF-8.
OCTET_LENGTH(str): Returns the length of a string in terms of bytes. This is the same as the default behavior of the LENGTH() function.
BIT_LENGTH(str): Returns the length of a string in bits. This is useful for binary data.

What is the difference between LENGTH() and CHAR_LENGTH()?

LENGTH() returns the number of bytes, while CHAR_LENGTH() returns the number of characters. With UTF-8, one character can occupy multiple bytes.

SELECT LENGTH(_utf8mb4'海豚') AS bytes, CHAR_LENGTH(_utf8mb4'海豚') AS characters;

The result is:

6, 2

That is 6 bytes and 2 characters.

Number of characters present in a string

All String Functions CHAR_LENGTH()

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

LENGTH : to get number of bytes in a string

Using whitespace with LENGTH()

Student Table

What is the difference between LENGTH() and CHAR_LENGTH()?

Subhendu Mohapatra