CHARACTER_LENGTH( )

This MySQL function returns the number of characters of a given string.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CHARACTER_LENGTH(string)

Explanation

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( ).

Examples

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 the names of some students are appearing garbled. We realize this is because we weren't prepared for non-Latin characters. We could enter an SQL statement like the following to find students with the names columns containing multi-byte 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 comparing them with the != operator to return only rows where the two methods of evaluation don't equal.