MySQL in a Nutshell, 2nd Edition

Name

CHARACTER_LENGTH()

Synopsis

CHARACTER_LENGTH(string)

This function returns the number of characters of a given string. A multiple-byte character is treated as one character. It’s synonymous with CHAR_LENGTH().

As another example of how this function or CHAR_LENGTH() might be used, suppose that in a college’s table containing students names we notice that some of the names appear garbled. We realize this is happening because we weren’t prepared for non-Latin characters. We could enter an SQL statement like the following to find students with the names containing multibyte characters:

SELECT student_id, 
CONCAT(name_first, SPACE(1), name_last) AS Name
FROM students
WHERE CHARACTER_LENGTH(name_first) != LENGTH(name_first)
OR CHARACTER_LENGTH(name_last) != LENGTH(name_last);

In this example, in the WHERE clause we’re using CHARACTER_LENGTH() to get the number of bytes and LENGTH() to get the number of characters for each name, and then we’re comparing them with the != operator to return only rows where the two methods of evaluation don’t equal.