What is the difference between MySQL LENGTH() and CHAR_LENGTH() function?

MySQLMySQLi Database

Both the functions are string functions and return the number of characters present in the string. But they differ in the concept that CHAR_LENGTH() function measures the string length in ‘characters’ whereas LENGTH() function measures the string length in ‘bytes’. In other words, we can say that CHAR_LENGTH() function is multi-byte safe i.e. it ignores whether the characters are single-byte or multi-byte. For example, if a string contains four 2-bytes characters then LENGTH().

The function will return 8, whereas CHAR_LENGTH() function will return 4. In this sense, we can say that CHAR_LENGTH() gives precise result than LENGTH() function.

The difference is especially relevant for Unicode, in which most of the characters are encoded in two bytes or relevant for UTF-8 where the number of bytes varies. It is demonstrated in the example below −

Example

In this example below, first of all, the string ‘Gaurav’ is converted into ucs2, that is Unicode and holds 2-byte characters, character set. Then we can observe the difference from the result set i.e. LENGTH() returns length in a number of bytes and CHAR_LENGTH() returns length in a number of characters.

mysql> SET @A = CONVERT('Gaurav' USING ucs2);
Query OK, 0 rows affected (0.15 sec)

mysql> Select Char_length(@A), LENGTH(@A);

+-----------------+------------+
| Char_length(@A) | LENGTH(@A) |
+-----------------+------------+
| 6               | 12         |
+-----------------+------------+

1 row in set (0.03 sec)

And now, in the example below, we are using a special character in UTF-8, a number of bytes vary, character set. Then we can observe the difference from the result set.

mysql> SET @B = CONVERT('©' USING utf8);
Query OK, 0 rows affected (0.15 sec)

mysql> Select CHAR_LENGTH(@B);

+-----------------------+
| CHAR_LENGTH(@B)       |
+-----------------------+
| 1                     |
+-----------------------+

1 row in set (0.00 sec)

mysql> Select LENGTH(@B);

+---------------+
| LENGTH(@B)    |
+---------------+
| 2             |
+---------------+

1 row in set (0.00 sec)
raja
Published on 19-Feb-2018 18:04:48
Advertisements