MySQL String Length

In SQL language the LENGTH() function allows to calculate the length of a string.

Note: the function is based on the number of bytes. A multi-byte character counts as a single character. In other words, a character such as “œ” counts as a single character.
 

Syntax:

This SQL function is used with the following structure:

SELECT LENGTH('example');

Output:

7

 
 

Example :

In this tutorial, we will consider a user table that contains the login, city, and country id.

+-----------+-----------+-----------+------------+
|  user_id  |   login   | user_city | country_id |
+-----------+-----------+-----------+------------+
|    1      |   Alex    | New York  |  1         |
|    2      |   Emily   | Chicago   |  1         |
|    3      |   Jean    | Mumbai    |  NULL      |
|    4      |   Bob     | Sydney    |  1555      |
|    5      |   Xavier  | London    |  4         |
+-----------+-----------+-----------+------------+

 

Find out the length of the login

It is possible to know the length of the login of each user thanks to the LENGTH() function. This length can be displayed using an SQL query like this one:

SELECT user_id, login, LENGTH(login) AS login_length, user_city, country_id
FROM users

Output:

+-----------+-----------+--------------+-----------+------------+
|  user_id  |   login   | login_length | user_city | country_id |
+-----------+-----------+--------------+-----------+------------+
|    1      |   Alex    | 4            | New York  |  1         |
|    2      |   Emily   | 5            | Chicago   |  1         |
|    3      |   Jean    | 4            | Mumbai    |  NULL      |
|    4      |   Bob     | 3            | Sydney    |  1555      |
|    5      |   Xavier  | 6            | London    |  4         |
+-----------+-----------+--------------+-----------+------------+

 
 

Extract users with a login shorter than 5 characters

Let’s imagine that for some reason, users with a login of less than 4 characters are not secure enough. It is possible to extract the list of these users with the LENGTH() function, as shown in the following query:

SELECT user_id, login, LENGTH(login) AS login_length, user_city, country_id
FROM users
WHERE LENGTH(login) < 4

Output:

+-----------+-----------+--------------+-----------+------------+
|  user_id  |   login   | login_length | user_city | country_id |
+-----------+-----------+--------------+-----------+------------+
|    4      |   Bob     | 3            | Sydney    |  1555      |
+-----------+-----------+--------------+-----------+------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More