length() vs char_length() in MySQL?

The char_length() can be used to display the length of a string. Let us see an example to get the length of the string included as a parameter.

mysql> select char_length('John');

The following is the output.

+---------------------+
| char_length('John') |
+---------------------+
| 4                   |
+---------------------+
1 row in set (0.00 sec)

The length() function can be used to display the length of string measured in bytes. In many cases characters and bytes gives the same length.

Here is an example of length()

mysql> select length('Tim');

The following is the output.

+---------------------+
| length('Tim')       |
+---------------------+
| 3                   |
+---------------------+
1 row in set (0.00 sec)

The length() and char_length() gives the same result in string. The length() function gives different length when it applies Unicode. Suppose a column name has unicode.In Unicode, every single character takes 2 bytes.

Let us see an example −

mysql> create table LengthAndCharLengthDemo
-> (
-> FirstName varchar(200),
-> SecondName varchar(255) unicode
-> );
Query OK, 0 rows affected (0.49 sec)

Inserting records into table.

mysql> insert into LengthAndCharLengthDemo values('John','Ritter');
Query OK, 1 row affected (0.22 sec)

Displaying all records from the table with the help of select.

mysql> select *from LengthAndCharLengthDemo;

Here is the output.

+-----------+------------+
| FirstName | SecondName |
+-----------+------------+
| John      | Ritter     |
+-----------+------------+
1 row in set (0.00 sec)

Let us get the length.

mysql> select char_length(FirstName),length(Secondname) from LengthAndCharLengthDemo
-> where FirstName='John' and Secondname='John';

Here is the output.

+------------------------+--------------------+
| char_length(FirstName) | length(SecondName) |
+------------------------+--------------------+
| 4                      | 8                  |
+------------------------+--------------------+
1 row in set (0.70 sec)

In the above output, you can see the length() function returns 8 bytes because of unicode while char_length() returns only 4.