{"id":2349,"date":"2013-05-14T22:31:41","date_gmt":"2013-05-15T06:31:41","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2349"},"modified":"2023-10-12T21:22:28","modified_gmt":"2023-10-13T04:22:28","slug":"mysql-string-length","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-string-functions\/mysql-string-length\/","title":{"rendered":"MySQL LENGTH() Function"},"content":{"rendered":"\n

Summary<\/b>: in this tutorial, you will learn about the MySQL LENGTH()<\/code> function to get the length of strings in bytes.<\/p>\n\n\n\n

Introduction to MySQL LENGTH() function<\/h2>\n\n\n\n

The LENGTH()<\/code> function returns the length of a string measured in bytes. <\/p>\n\n\n\n

Here’s the basic syntax of the LENGTH()<\/code> function:<\/p>\n\n\n

LENGTH(string)<\/code><\/span><\/pre>\n\n\n
The LENGTH() function takes a string argument and returns its length measured in bytes. Therefore, the result of the LENGTH()<\/code> function is based on the current character set<\/a> of the string<\/code>.<\/p>\n\n\n\n
Note that to get the number of characters of a string, you use the CHAR_LENGTH()<\/code> function instead.<\/p>\n\n\n\n
Understanding character sets<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
To find available character sets in the current database, you use the following statement:<\/p>\n\n\n
SHOW CHARACTER SET;<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------+---------------------------------+---------------------+--------+\n| Charset  | Description                     | Default<\/span> collation   | Maxlen |\n+----------+---------------------------------+---------------------+--------+\n| armscii8 | ARMSCII-8<\/span> Armenian              | armscii8_general_ci |      1<\/span> |\n| ascii    | US ASCII                        | ascii_general_ci    |      1<\/span> |\n| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2<\/span> |\n| binary   | Binary pseudo charset           | binary              |      1<\/span> |\n...<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
In the output, the Maxlen<\/code> column shows the maximum number of bytes for each character.<\/p>\n\n\n\n
To find the character set that the current database uses, you use the @@character_set_database variable:<\/p>\n\n\n
SELECT<\/span> @@character_set_database<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------------+\n| @@character_set_database |\n+--------------------------+\n| latin1                   |\n+--------------------------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
For the rest of the tutorial, we assume that you use the latin1<\/code> as the default character set.<\/p>\n\n\n\n
MySQL LENGTH function examples<\/h2>\n\n\n\n
Let’s take some examples of using the LENGTH()<\/code> function.<\/p>\n\n\n\n
1) Single-byte character set example<\/h3>\n\n\n\n
The following example uses the LENGTH()<\/code> function to return the length of a string in the Latin1 character set:<\/p>\n\n\n
SELECT LENGTH('Hello'<\/span>) as<\/span> length;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------+\n| length |\n+--------+\n|      5<\/span> |\n+--------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The query returns 5 because the string 'Hello'<\/code> contains five characters and each character is represented using a single byte in the latin1<\/code> character set.<\/p>\n\n\n\n
2) Multi-byte character set example<\/h3>\n\n\n\n
The following example uses the LENGTH()<\/code> to get the length of the string Caf\u00e9<\/code> in latin1<\/code> and utf8mb3<\/code> character sets:<\/p>\n\n\n
SELECT<\/span> \n  LENGTH<\/span>('Caf\u00e9'<\/span>) length_latin1,\n  LENGTH<\/span>(\n    CONVERT<\/span>('Caf\u00e9'<\/span> USING<\/span> utf8mb3)\n  ) length_utf8;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------------+-------------+\n| length_latin1 | length_utf8 |\n+---------------+-------------+\n|             4<\/span> |           5<\/span> |\n+---------------+-------------+\n1<\/span> row in<\/span> set<\/span>, 1 warning (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example: <\/p>\n\n\n\n
    \n
  • The first LENGTH()<\/code> function returns 4 because latin1 represents each character using 1 byte. <\/li>\n\n\n\n
  • The second LENGTH()<\/code> function returns 5 because utf8mb3 uses one byte to represent the first three characters (caf<\/code>) two bytes to represent the last character (\u00e9<\/code>).<\/li>\n<\/ul>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the MySQL LENGTH()<\/code> function to get the length of a string in bytes.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t