Summary: in this tutorial, you will learn about MySQL string length functions that allow you to get the length of strings measured in bytes and in characters.

MySQL supports various character sets such as latin1, utf8, etc. You use the SHOW CHARACTER SET  statement to get all character sets supported by MySQL database server.

Code language: SQL (Structured Query Language) (sql)

The Maxlen column stores the number of bytes for character sets. In MySQL, a string can be in any character set. If a string contains 1-byte characters, its length measured in characters and its length measured in bytes are equal. However, if a string contains multi-byte characters, its length in bytes is typically greater than its length in characters.

To get the length of a string measured in bytes, you use the LENGTH  function as follows:

Code language: SQL (Structured Query Language) (sql)

You use the CHAR_LENGTH  function to get the length of a string measured in characters as follows:

Code language: SQL (Structured Query Language) (sql)

Examples of LENGTH and CHAR_LENGTH functions

Let’s take a look at the following statements:

Code language: SQL (Structured Query Language) (sql)

How it works.

• First, we convert the MySQL String Length  string into ucs2 character set, which is UCS-2  Unicode that holds 2-byte characters.
• Second, we use the CHAR_LENGTH and LENGTH functions to get the length of the @s  string in bytes and in characters. Because the @s  string contains 2-byte characters, its length in character is 19, while its length in bytes is 38.

The following statements demonstrate how the LENGTH and CHAR_LENGTH functions that work with 1-byte characters:

Code language: SQL (Structured Query Language) (sql)

We use the latin1 character set for the @s  string. The latin1 character set contains 1-byte characters; therefore, its length in bytes and its length in character are equal.

Notice that some character sets hold characters whose number of bytes can be varied e.g., for the utf8 character set:

Code language: SQL (Structured Query Language) (sql)

The CHAR_LENGTH and LENGTH returns the same result. However, if a string has special characters, the result is different. See the following example:

Code language: SQL (Structured Query Language) (sql)

An application of MySQL string length functions

Suppose we have a posts table that stores blog posts with four columns postid, title, excerpt and content. (We make the posts table as simple as possible for the demonstration purpose).

First, we create the posts table by using the CREATE TABLE statement:

Code language: SQL (Structured Query Language) (sql)

Second, we insert some blog posts into the posts table by using the INSERT statement:

Code language: SQL (Structured Query Language) (sql)

We can use the CHAR_LENGTH function to check if the except has more than 20 characters, we append an ellipsis (…) to the excerpt as the following query:

Code language: SQL (Structured Query Language) (sql)

In the SELECT statement, we use the IF  function to check if the length of the excerpt column is greater than 20, we concatenate the excerpt with the ellipsis (…) by using the CONCAT statement, otherwise we just get the excerpt.

In this tutorial, we have shown you how to use MySQL string length functions to get the length of a string in bytes and in characters.

Reference

• http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_length – MySQL LENGTH function
• http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_char-length – MySQL CHAR_LENGTH function