LENGTH() function in MySQL returning wrong rows

user_16023777 0
Viewed 41

So I am trying to do this one,

"Write a query to get the details of the employees where the length of the first name greater than or equal to 8"

and the expected outcome is this,

expected

So what I did is

SELECT * FROM employees WHERE LENGTH(fname) >= 8;

however whenever I try to execute the code, the returned rows are not first names whose length is greater than or equal to 8

returned

EDIT: Additional picture output

EDIT 2: it seems that from the file that I have imported, the columns for first name has whitespaces

mysql
1 Answers

It looks like your values contain leading or trailing whitespace. Use TRIM() in addition to LENGTH(), eg LENGTH(TRIM(fname)) >= 8.

In addition the LENGTH() function measures the size of the string in bytes, whereas you are concerned with the size of the string in characters. All of which means depending on your character set you will get different answers for your query.

CREATE TABLE employees(employee_id INT, fname VARCHAR(20)) CHARSET utf8mb4;
INSERT INTO employees VALUES
    (100, 'Steven'), (101, 'Neena'), (102, 'Lex'), (103, 'Alexander');
SELECT * FROM employees WHERE LENGTH(TRIM(fname)) >= 8;
-- +-------------+-----------+
-- | employee_id | fname     |
-- +-------------+-----------+
-- |         103 | Alexander |
-- +-------------+-----------+
-- 1 row in set (0.00 sec)

ALTER TABLE employees CONVERT TO CHARSET utf16;
SELECT * FROM employees WHERE LENGTH(TRIM(fname)) >= 8;
-- +-------------+-----------+
-- | employee_id | fname     |
-- +-------------+-----------+
-- |         100 | Steven    |
-- |         101 | Neena     |
-- |         103 | Alexander |
-- +-------------+-----------+
-- 3 rows in set (0.00 sec)

ALTER TABLE employees CONVERT TO CHARSET utf32;
SELECT * FROM employees WHERE LENGTH(TRIM(fname)) >= 8;
-- +-------------+-----------+
-- | employee_id | fname     |
-- +-------------+-----------+
-- |         100 | Steven    |
-- |         101 | Neena     |
-- |         102 | Lex       |
-- |         103 | Alexander |
-- +-------------+-----------+
-- 4 rows in set (0.00 sec)

The alternative CHAR_LENGTH() function measures size in characters.

SELECT * FROM employees WHERE CHAR_LENGTH(TRIM(fname)) >= 8;
-- +-------------+-----------+
-- | employee_id | fname     |
-- +-------------+-----------+
-- |         103 | Alexander |
-- +-------------+-----------+
-- 1 row in set (0.00 sec)
user_50245190
Related