MySQL String Length

 MySQL String Length

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 the MySQL database server.

SHOW CHARACTER SET;

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 is 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:

LENGTH(str);

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

CHAR_LENGTH(str);

Examples of LENGTH and CHAR_LENGTH functions

Let’s take a look at the following statements:

SET @s = CONVERT('MySQL String Length' USING ucs2);
SELECT CHAR_LENGTH(@s), LENGTH(@s);

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  the 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:

SET @s = CONVERT('MySQL string length' USING latin1);
SELECT LENGTH(@s), CHAR_LENGTH(@s);

We use the latin1 character set for the @s  string. The latin1 the 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:

SET @s = CONVERT('MySQL String Length' USING utf8);
SELECT CHAR_LENGTH(@s), LENGTH(@s);

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

SET @s = CONVERT('á' USING utf8);
SELECT CHAR_LENGTH(@s), LENGTH(@s);

An application of MySQL string length functions

Suppose we have a posts the 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:

CREATE TABLE posts(
  postid int auto_increment primary key,
  title varchar(255) NOT NULL,
  excerpt varchar(255) NOT NULL,
  content text,
  pubdate datetime
)Engine=InnoDB;

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

INSERT INTO posts(title,excerpt,content)
VALUES('MySQL Length','MySQL string length function tutorial','dummy'),
      ('Second blog post','Second blog post','dummy');

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

SELECT postid,
       title,
       IF(CHAR_LENGTH(excerpt) > 20,
          CONCAT(LEFT(excerpt,20), '...'),
          excerpt) summary
FROM posts;

In the SELECT statement, we use the IF  function to check if the length of the excerpt the column is greater than 20, we concatenate the excerpt with the ellipsis (…) by using the CONCAT the 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