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

We can get number of bytes present against each data can be found by using LENGTH command.

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)

Now let us try to find some blank data using length query

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

This will display records where Mark is single digit or not entered.

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.
LENGTHB(str): This function is an alias for OCTET_LENGTH(str), returning the length of a string in bytes.
BIT_LENGTH(str): Returns the length of a string in bits. This is useful for binary data.
LENGHTCOUNE(str): This function returns the number of characters in a string. If the string contains multi-byte characters, this function counts them as one character.

What is the difference between length and char_length ?

Length returns string measured in bytes, but char_length returns number of characters. All chars does not occupy single byte particularly Unicode where chars are encoded in two bytes. Here is an example.

SELECT LENGTH( _utf8 '¥' ) , CHAR_LENGTH( _utf8 '¥' )

Output is here

2,1

Number of characters present in a string
SQL String References CHAR_LENGTH() substring_index to get part of string using delimiter

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