LENGTH

Description

Get the length of a string in bytes.

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       
cp1250    Windows Central European         cp1250_general_ci    1       
cp1251    Windows Cyrillic                 cp1251_general_ci    1       
cp1256    Windows Arabic                   cp1256_general_ci    1       
cp1257    Windows Baltic                   cp1257_general_ci    1       
cp850     DOS West European                cp850_general_ci     1       
cp852     DOS Central European             cp852_general_ci     1       
cp866     DOS Russian                      cp866_general_ci     1       
cp932     SJIS for Windows Japanese        cp932_japanese_ci    2       
dec8      DEC West European                dec8_swedish_ci      1       
eucjpms   UJIS for Windows Japanese        eucjpms_japanese_ci  3       
euckr     EUC-KR Korean                    euckr_korean_ci      2       
gb18030   China National Standard GB18030  gb18030_chinese_ci   4       
gb2312    GB2312 Simplified Chinese        gb2312_chinese_ci    2       
gbk       GBK Simplified Chinese           gbk_chinese_ci       2       
geostd8   GEOSTD8 Georgian                 geostd8_general_ci   1       
greek     ISO 8859-7 Greek                 greek_general_ci     1       
hebrew    ISO 8859-8 Hebrew                hebrew_general_ci    1       
hp8       HP West European                 hp8_english_ci       1       
keybcs2   DOS Kamenicky Czech-Slovak       keybcs2_general_ci   1       
koi8r     KOI8-R Relcom Russian            koi8r_general_ci     1       
koi8u     KOI8-U Ukrainian                 koi8u_general_ci     1       
latin1    cp1252 West European             latin1_swedish_ci    1       
latin2    ISO 8859-2 Central European      latin2_general_ci    1       
latin5    ISO 8859-9 Turkish               latin5_turkish_ci    1       
latin7    ISO 8859-13 Baltic               latin7_general_ci    1       
macce     Mac Central European             macce_general_ci     1       
macroman  Mac West European                macroman_general_ci  1       
sjis      Shift-JIS Japanese               sjis_japanese_ci     2       
swe7      7bit Swedish                     swe7_swedish_ci      1       
tis620    TIS620 Thai                      tis620_thai_ci       1       
ucs2      UCS-2 Unicode                    ucs2_general_ci      2       
ujis      EUC-JP Japanese                  ujis_japanese_ci     3       
utf16     UTF-16 Unicode                   utf16_general_ci     4       
utf16le   UTF-16LE Unicode                 utf16le_general_ci   4       
utf32     UTF-32 Unicode                   utf32_general_ci     4       
utf8mb3   UTF-8 Unicode                    utf8mb3_general_ci   3       
utf8mb4   UTF-8 Unicode                    utf8mb4_0900_ai_ci   4

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;

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.

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;

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;

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.