How to Check the Length of a String in SQL

Database:

Operators:

LENGTH()
ORDER BY
CHAR_LENGTH()

Table of Contents

Problem:
Example:
Solution:
Discussion:

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.

id	first_name	last_name	city
1	Owen	Taylor	Manchester
2	Harold	Wilkinson	Manchester
3	Karl	Gavers	Dallas
4	Mary	Larson	Dallas

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:

id	last_name	city	length
3	Gavers	Dallas	6
4	Larson	Dallas	6
1	Taylor	Manchester	10
2	Wilkinson	Manchester	10

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.

Problem:

Example:

Solution:

Discussion:

Recommended courses:

Recommended articles:

See also: