Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is the difference between MySQL LENGTH() and CHAR_LENGTH() function?
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)
2K+ Views
