MySQL in a Nutshell, 2nd Edition

Name

CHAR_LENGTH()

Synopsis

CHAR_LENGTH(string)

This function returns the number of characters in a given string. This is synonymous with CHARACTER_LENGTH(). A multiple-byte character is treated as one character. Use LENGTH() if you want each byte to be counted. Here is an example:

SELECT course_id,
   CASE
   WHEN CHAR_LENGTH(course_desc) > 30
   THEN CONCAT(SUBSTRING(course_desc, 1, 27), '...')
   ELSE course_desc
   END AS Description
FROM courses;

In this example, a CASE control statement is used to specify different display results based on a condition. Using the CHAR_LENGTH() function, MySQL determines whether the content of course_desc is longer than 30 characters. If it is, the SUBSTRING() function extracts the first 27 characters and the CONCAT() function adds ellipsis points to the end of the truncated data to indicate that there is more text. Otherwise, the full contents of course_desc are displayed. See the CHARACTER_LENGTH() description next for another example of how CHAR_LENGTH() may be used.