Name

CHAR_LENGTH( )

Synopsis

CHAR_LENGTH(string)

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

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 if 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.

Get MySQL in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.