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.
Get MySQL in a Nutshell, 2nd Edition 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.