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!