MySQL in a Nutshell, 2nd Edition

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.