Name
LENGTH()
Synopsis
LENGTH(string
)
This function returns the number of bytes contained in a given string. It is not aware of multibyte characters, so it assumes there are eight bits to a byte and one byte to a character. OCTET_LENGTH() is an alias. If you want to get the length of characters regardless of whether a character is multibyte or not, use CHARACTER_LENGTH().
As an example, suppose that we notice in an online survey that some odd binary characters have been entered into the data through the web interface—probably from a spam program. To narrow the list of rows, we can enter the following statement to find the rows that have binary characters in three columns that have the bad data:
SELECT respondent_id FROM survey WHERE CHARACTER_LENGTH(answer1) != LENGTH(answer1) OR CHARACTER_LENGTH(answer2) != LENGTH(answer2) OR CHARACTER_LENGTH(answer3) != LENGTH(answer3) survey_id = 127;
In this example, the WHERE
clause invokes CHARACTER_LENGTH() to get the number
of bytes, and LENGTH() to get the number of
characters for each column containing a respondent’s answers to the
survey questions. We then compare them with the !=
operator to return only rows in which the two methods of evaluation
are not equal. The LENGTH()
will return a
greater value for multibyte characters, whereas
CHARACTER_LENGTH()
will return 1 for each
character, regardless of whether it’s a multibyte character.
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.