MySQL CHAR_LENGTH() function

CHAR_LENGTH() function

MySQL CHAR_LENGTH() returns the length (how many characters are there) of a given string. The function simply counts the number characters and ignore whether the character(s) are single-byte or multi-byte. Therefore a string containing three 2-byte characters, LENGTH() function will return 6, whereas CHAR_LENGTH() function will returns 3.

This function is useful in -

• It allows us to determine the length of a string in terms of the number of characters it contains.
• We can use CHAR_LENGTH() to validate string length against specific criteria. For example, you can check if a username is within an acceptable length range before inserting it into a database.
• The CHAR_LENGTH() function is often used in data manipulation tasks, such as substring extraction or truncation based on a specific character length.

Syntax:

CHAR_LENGTH (string)

Argument:

Syntax Diagram:

MySQL Version: 8.0

Example: MySQL CHAR_LENGTH() function

The following MySQL statement will count the length of the string specified as an argument.

Code:

SELECT CHAR_LENGTH('test string');

Output:

mysql> SELECT CHAR_LENGTH('test string');
+----------------------------+
| CHAR_LENGTH('test string') |
+----------------------------+
|                         11 | 
+----------------------------+
1 row in set (0.00 sec)

Example of MySQL CHAR_LENGTH() function with where clause

The following MySQL statement will count how many characters are there in the names of publishers (pub_name) from the publisher table, and returns the name and number of characters in the names if the name has more than twenty characters.

Code:

SELECT pub_name,
CHAR_LENGTH(pub_name)  AS 'character length' 
FROM publisher 
WHERE CHAR_LENGTH(pub_name)>20;
 

Sample table: publisher

Output:

mysql> SELECT pub_name,
    -> CHAR_LENGTH(pub_name) AS 'character length'
    -> FROM publisher
    -> WHERE CHAR_LENGTH(pub_name)>20;
+------------------------------+------------------+
| pub_name                     | character length |
+------------------------------+------------------+
| New Harrold Publication      |               23 | 
| Summer Night Publication     |               24 | 
| Pieterson Grp. of Publishers |               28 | 
+------------------------------+------------------+
3 rows in set (0.00 sec)

MySQL - LENGTH() vs CHAR_LENGTH()

We have already discussed in description section that LENGTH() function returns the length of the string calculated by number of bytes whereas CHAR_LENGTH() function returns the length of the string calculated by number of characters. See the following example :

mysql> select length('§'), char_length('§');
+------------+-----------------+
| length('§')| char_length('§')|
+------------+-----------------+
| 2          | 1               |
+------------+-----------------+
1 row in set (0.00 sec)

The section sign (§, Unicode U+00A7) occupies 2 bytes even though it is a single character.

Video Presentation:

All String Functions (Slides presentation)

Previous: BIT_LENGTH
Next: CHAR