SELECT * FROM WHERE does not find all records

Question

When I type:

select * from 'saleslog' where 'Status' = 'Pending';

or

select * from 'saleslog' where 'Status' = "Pending";

or

select * from saleslog where Status = 'Pending';

despite the fact that there are hundreds of rows with "Pending" value in Status column I get only 3 records displayed. The same happens when I look for value other than "Pending".

If however, I type:

select * from saleslog where status like "%Pending%";

then most if not all records are displayed. There are absolutely no spaces or any other characters in front and behind Pending value.

I am wondering if table "saleslog" needs to be repaired and if so, how? I'm kind of new to SQL.

Solution 1

It's possible there are hidden characters in the field that you just can't see such as tab, carriage return or line feed. Have you tried doing an update on the field to try and correct it? Maybe try running the update query below and then run your SELECT query again:

UPDATE saleslog SET status = 'Pending' WHERE status LIKE '%Pending%'  

Solution 2

Try the following:

UPDATE `saleslog` SET `status` = TRIM(`status`);
SELECT * FROM `saleslog` WHERE `status` = 'Pending';

Comments

[user3387040]

I get "ERROR 1064 (42000): You have an error in your SQL Syntax:..."

[user3387040]

I also use third party PHP software like Adminder Editor and searching for records in it brings to same results.

[user3387040]

Oh, I have just copied and pasted your code and it did displayed three records with "Pending" value in Status column. No error this time - perhaps I did typo first time. Still remaining 100s of records were not found/displayed.

[user3387040]

UPDATE saleslog SET status = 'Pending' WHERE status LIKE '%Pending%' Query OK, 240 rows affected (0.21 sec) Rows matched: 243 Changed: 240 Warnings: 0 THIS DID THE TRICK. Thank you! Seems like all rows are now being displayed.

[user3387040]

I have tried as you have suggested and here is what I've got, but then I did what cmreynol suggested and seems it worked. mysql> UPDATE saleslog SET status = TRIM(status); Query OK, 0 rows affected (0.01 sec) Rows matched: 2007 Changed: 0 Warnings: 0 SELECT * FROM saleslog WHERE status = 'Pending'; Table here... 3 rows in set (0.00 sec)