Detailed explanation of the usage of CHARACTER_LENGTH() function in MySQL

In MySQL, the CHARACTER_LENGTH() function returns the length of the string in characters.

CHARACTER_LENGTH() is a synonym for the CHAR_LENGTH() function.

The syntax is like this:

CHARACTER_LENGTH(str)

where str is the string that returns the length.

Example 1 - Basic usage

The following is an example of basic usage:

SELECT CHARACTER_LENGTH('Cat');

The result is like this:

+-------------------------+
| CHARACTER_LENGTH('Cat') |
+-------------------------+
|                       3 |
+-------------------------+

Example 2 - Spaces at the end

Note that CHARACTER_LENGTH() includes trailing spaces (such as spaces at the end of a string) in its calculations.

So if we add space at the end of the previous example:

SELECT CHARACTER_LENGTH('Cat ');

Result:

+--------------------------+
| CHARACTER_LENGTH('Cat ') |
+--------------------------+
|                        4 |
+--------------------------+

But we can use TRIM() function or RTRIM() function to remove Following spaces:

SELECT 
  CHARACTER_LENGTH(TRIM('Cat ')) AS 'TRIM',
  CHARACTER_LENGTH(RTRIM('Cat ')) AS 'RTRIM';

Result:

+------+-------+
| TRIM | RTRIM |
+------+-------+
|    3 |     3 |
+------+-------+

Example 3 - Preceded by spaces

The same concept applies to preceding spaces. You can use TRIM or LTRIM:

SELECT 
  CHARACTER_LENGTH(TRIM(' Cat')) AS 'TRIM',
  CHARACTER_LENGTH(LTRIM(' Cat')) AS 'LTRIM';

Result:

+------+-------+
| TRIM | LTRIM |
+------+-------+
|    3 |     3 |
+------+-------+

Example 4 - Data Type

No matter what data type the string is stored in , it will all return the same result. This is in contrast to the LENGTH() function, which will return double the number of characters if the data is stored as a Unicode string.

In the example below, the ArtistName column uses varchar(255):

SELECT CHARACTER_LENGTH(ArtistName) Result
FROM Artists
WHERE ArtistName = 'Lit';

The result:

+--------+
| Result |
+--------+
|      3 |
+--------+

If we modify the ArtistName column to use Unicode:

ALTER TABLE Artists 
MODIFY COLUMN ArtistName VARCHAR(255) unicode;

And run the same query again:

SELECT CHARACTER_LENGTH(ArtistName) 
FROM Artists
WHERE ArtistName = 'Lit';

We still get the same result:

+--------+
| Result |
+--------+
|      3 |
+--------+

However, if we use the LENGTH() function, the result will be 6. This is because Unicode strings store 2 bytes per character and the LENGTH() function returns the length measured in bytes.

Related recommendations: "mysql tutorial"

The above is the detailed content of Detailed explanation of the usage of CHARACTER_LENGTH() function in MySQL. For more information, please follow other related articles on the PHP Chinese website!