Count characters with MySQL

We make use of the functions to count characters in MySQL. Several examples of an SQL statement that counts characters from strings.

In this MySQL workshop we are going to use the SQL language functions to count characters from a field stored in a table. That is, we are going to select various records from a table and we are going to count the characters in one of its fields.

In the MySQL function set for string we have several functions to count characters:

LENGTH(string)

Receives a string and counts and returns the number of characters.

CHARACTER_LENGTH(string)

Also receives a string and returns the number of characters counted.

CHAR_LENGTH(string)

It is a synonym for CHARACTER_LENGTH. Although not all versions of MySQL have it.

The difference between LENGTH and CHARACTER_LENGTH is that in CHARACTER_LENGTH a “multibyte” character counts as a single character. In LENGTH it counts the number of bytes in the string. So in the case of having a string with 5 characters that occupy 2 bytes each, LENGTH would return 10 and CHARACTER_LENGTH only 5.

For now we are going to use CHARACTER_LENGTH, which seems to return the results we expect to get, the number of characters in the string, regardless of whether the encoding may be multi-byte.

SELECT character_length( ‘some text’ )

This statement would return the value 8, which is the number of characters that the string “a text” has.

But now let’s say we want to retrieve all the countries from a table, along with the number of characters each of the country name strings has.

SELECT CHARACTER_LENGTH(country_name), country_name FROM country

This would return a recordset with the number of characters for each country name and then another field with the country name.

SELECT country_name FROM country where CHARACTER_LENGTH(country_name)=6

This returns country names that are 6 characters long.

SELECT CHARACTER_LENGTH(country_name) as ‘numcharacters’, country_name FROM country ORDER BY CHARACTER_LENGTH(country_name)

This last sentence returns a set of records where the first field, which we have given the name “numcharacters” to refer to later, shows the number of characters and the second field the name of the country. It is then sorted by the length of the string, also counted by its characters.