MySQL LENGTH() Function

Summary: in this tutorial, you will learn about the MySQL LENGTH() function to get the length of strings in bytes.

Introduction to MySQL LENGTH() function

The LENGTH() function returns the length of a string measured in bytes.

Here’s the basic syntax of the LENGTH() function:

LENGTH(string)

The LENGTH() function takes a string argument and returns its length measured in bytes. Therefore, the result of the LENGTH() function is based on the current character set of the string.

Note that to get the number of characters of a string, you use the CHAR_LENGTH() function instead.

Understanding character sets

Character sets define how MySQL store and represent characters in a database. MySQL supports various character sets, including single-byte and multi-byte character sets.

In a single-byte character set, MySQL represents each character using a single byte. For these character sets, the length of a string in characters is equal to its length in bytes.

In multi-byte character sets, such as UTF-8, MySQL represents characters using multiple bytes. In such cases, the length of a string in character may be different from its length in bytes.

To find available character sets in the current database, you use the following statement:

SHOW CHARACTER SET;

Output:

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
...Code language: PHP (php)

In the output, the Maxlen column shows the maximum number of bytes for each character.

To find the character set that the current database uses, you use the @@character_set_database variable:

SELECT @@character_set_database;Code language: CSS (css)

Output:

+--------------------------+
| @@character_set_database |
+--------------------------+
| latin1                   |
+--------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

For the rest of the tutorial, we assume that you use the latin1 as the default character set.

MySQL LENGTH function examples

Let’s take some examples of using the LENGTH() function.

1) Single-byte character set example

The following example uses the LENGTH() function to return the length of a string in the Latin1 character set:

SELECT LENGTH('Hello') as length;Code language: JavaScript (javascript)

Output:

+--------+
| length |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The query returns 5 because the string 'Hello' contains five characters and each character is represented using a single byte in the latin1 character set.

2) Multi-byte character set example

The following example uses the LENGTH() to get the length of the string Café in latin1 and utf8mb3 character sets:

SELECT 
  LENGTH('Café') length_latin1,
  LENGTH(
    CONVERT('Café' USING utf8mb3)
  ) length_utf8;
Code language: SQL (Structured Query Language) (sql)

Output:

+---------------+-------------+
| length_latin1 | length_utf8 |
+---------------+-------------+
|             4 |           5 |
+---------------+-------------+
1 row in set, 1 warning (0.00 sec)Code language: JavaScript (javascript)

In this example:

The first LENGTH() function returns 4 because latin1 represents each character using 1 byte.
The second LENGTH() function returns 5 because utf8mb3 uses one byte to represent the first three characters (caf) two bytes to represent the last character (é).

Summary

Use the MySQL LENGTH() function to get the length of a string in bytes.

Was this tutorial helpful?