How to Check the Length of a String in SQL

Problem:

You want to know the number of characters in a string.

Example:

Our database has a table named employees with data in the following columns: id (primary key), first_name, last_name, and city.

Let’s say that we want to select the ID, last name, and city of each employee. However, we want to sort them by the length of the city name and then by employees’ last names.

Solution:

We can use the LENGTH() function to get the string length:

SELECT id,
 last_name,
 city,
 LENGTH(city) as length
FROM employees
ORDER BY length,
 last_name;

And here is the result:

Discussion:

LENGTH() returns the number of characters in a given string. It has only one parameter – the string itself. If the input string is empty, the function returns 0; if the string is NULL, the function also returns NULL.

In MySQL, LENGTH() returns the length of the string in bytes (not the number of characters). This means that if a string contains multibyte characters, its length in bytes can be greater than in characters. If a string contains only 1-byte characters (which is often the case), its length measured in characters and in bytes will be the same.

To return specifically the number of characters in a string in MySQL, the CHAR_LENGTH() function is used. It has the same parameter as LENGTH: an input string.