LENGTH : to get number of bytes in 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
L1L2
610

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:
  1. 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.
  2. 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.
  3. LENGTHB(str): This function is an alias for OCTET_LENGTH(str), returning the length of a string in bytes.
  4. BIT_LENGTH(str): Returns the length of a string in bits. This is useful for binary data.
  5. 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

Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer