LENGTH( )

This MySQL function returns the number of bytes contained in a given string.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

LENGTH(string)

Explanation

This function returns the number of bytes contained in a given string. It is not aware of multi-byte characters, so it assume 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 charcter is multi-byte or not, use CHARACTER_LENGTH().

Examples

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're then comparing them with the != operator to return only rows where the two methods of evaluation don't equal. The LENGTH( ) will return a greater value for multi-byte characters, whereas CHARACTER_LENGTH( ) with return 1 for each character whether it's a multi-byte character or not.