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
utf8, etc. You use the
SHOW CHARACTER SET statement to get all character sets supported by MySQL database server.
SHOW CHARACTER SET;
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:
You use the
CHAR_LENGTH function to get the length of a string measured in characters as follows:
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 Lengthstring into
ucs2character set, which is
UCS-2Unicode that holds 2-byte characters.
- Second, we use the
LENGTHfunctions to get the length of
the @sstring in bytes and in characters. Because the
@sstring contains 2-byte characters, its length in character is 19, while its length in bytes is 38.
The following statements demonstrate how the
CHAR_LENGTH functions that work with 1-byte characters:
SET @s = CONVERT('MySQL string length' USING latin1); SELECT LENGTH(@s), CHAR_LENGTH(@s);
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 varies e.g., for the
utf8 character set:
SET @s = CONVERT('MySQL String Length' USING utf8); SELECT CHAR_LENGTH(@s), LENGTH(@s);
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 table that stores blog posts with four columns
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 as 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 column is greater than 20, we concatenate the
excerpt with the ellipsis (…) by using the
CONCAT statement, otherwise we just get the
In this tutorial, we have shown you how use MySQL string length functions to get the length of a string in bytes and in characters.
- 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