MySQL LENGTH() Function

The LENGTH() function returns the length of its parameter in bytes.

You can also use the OCTET_LENGTH() function, as it does the same thing.

Here's an example:

SELECT 

title, 

   LENGTH(title),

OCTET_LENGTH(title)

FROM film;

Result:

In the above example, we select the title field from the film table. We then return the length of the value in bytes, first using LENGTH(title), then using OCTET_LENGTH(title).

The LENGTH() function should not be confused with CHAR_LENGTH(), which returns the number of characters in its argument.

Character Sets

The length returned will depend on the character set being used. The above examples use latin1, which result in 1 byte per character. Therefore in this case, LENGTH() will return the same value as CHAR_LENGTH().

Like this:

SELECT 

title, 

   LENGTH(title),

CHAR_LENGTH(title)

FROM film;

Result:

However, if we convert the title into a string that uses a multi-byte character set, we might see that LENGTH() and CHAR_LENGTH() return different values.

Here's an example:

SELECT 

title, 

   CHAR_LENGTH(title) 'Character Count',

   LENGTH(title) 'Latin1 (Bytes)',

   LENGTH(CONVERT(title USING ucs2)) 'UCS-2 (Bytes)',

   LENGTH(CONVERT(title USING utf32)) 'UTF-32 (Bytes)'

FROM film;

Result:

In the above example, first we return the title, then the number of characters in the title, then the length in bytes using Latin1 (the default), UCS-2, and UTF-32.

Display Character Sets

You can use the SHOW CHARACTER SET statement to display a list of all available character sets.

Example:

LENGTH() vs CHAR_LENGTH()

It's important not to get confused between the LENGTH() and CHAR_LENGTH() functions. This is especially true, given that the result may appear to be correct even if it's not. Therefore, it might not be immediately apparent that you're using the wrong command.

Here's a quick summary of each command (as well as their synonyms):

CHAR_LENGTH()

CHARACTER_LENGTH()

Returns the number of characters in its argument.

LENGTH()

OCTET_LENGTH()

Returns the length of a string in bytes (not the number of characters).