MySQL LENGTH Function | Length of String

MySQL LENGTH is an inbuilt function in MySQL. It returns the length of the input string according to a total number of bytes of the characters. Therefore, it does not ignore whether the characters are single-byte or multi-bytes. It counts a single byte character as 1 and a 2-byte character as 2. In this article, we will discuss the MySQL LENGTH Function. Also, we will discuss a few examples of using it.

Syntax

LENGTH (string)

Parameters

The MySQL LENGTH Function expects only one parameter. The string whose length you need to calculate is the input to the function.

Return Value

The function returns the length of the string by according to the number of bytes in the string. Also, it does not ignore whether characters are a single byte or multibyte. Therefore, the function will return 6 for a string containing three 2-byte characters.

Examples

Let’s consider a few examples of using the MySQL LENGTH Function.

Example 1: Single Byte Characters

For instance, consider a simple case of calculating the length of a string.

mysql> SELECT LENGTH ("Test String"), CHAR_LENGTH ("Test String");
+------------------------+-----------------------------+
| LENGTH ("Test String") | CHAR_LENGTH ("Test String") |
+------------------------+-----------------------------+
|                     11 |                          11 |
+------------------------+-----------------------------+

In the above example, the string contains 11 1-byte characters. The function returns the length of the string as the number of bytes.

Also, you can notice that the MySQL CHAR_LENGTH Function also returns the same output for a single byte character string.

Example 2: Two Byte Characters

Now, let us compare the behaviour of the function in case of Two Byte Characters.

mysql> SELECT LENGTH ("á"), CHAR_LENGTH ("á");
+---------------+--------------------+
| LENGTH ("á")  | CHAR_LENGTH ("á")  |
+---------------+--------------------+
|             2 |                  1 |
+---------------+--------------------+

In the above example, the LENGTH Function returns 2 as the character contains 2 bytes. On the other hand, the CHAR_LENGTH Function returns 1 as it only counts the number of characters in the string.

CHAR_LENGTH Vs LENGTH Function

MySQL supports various character sets. You can get a list of supported character sets by running the following command:

SHOW CHARACTER SET;

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |

The Maxlen column displays the number of bytes of a character for character sets. A string can be of any character set. For example, if a string belongs to ‘ujis’ character set, the MySQL LENGTH Function will return 6 for a two character string in ujis character set. On the other hand, the CHAR_LENGTH Function will return 2 for the same string.

Conclusion

In this article, we discussed the MySQL LENGTH Function. You can read more about it on the Official MySQL Documentation. Additionally, you can learn about more MySQL String Functions on Concatly.