MySQL simple query returns zero rows [duplicate]

Question

I have a table Types that has the following columns: ID, Name, Type. The table is filled with about 300 rows. One of the rows:

ID    Name    Type
------------------
1     BMW     S 1000 RR

The following query returns this row:

SELECT * FROM Types WHERE Name = 'BMW'

However, the following query returns nothing:

SELECT * FROM Types WHERE Type = 'S 1000 RR'

There are no extra spaces in the Type, and the data types of Name and Type are exactly the same (varchar 255, utf8_unicode_ci). What can possibly cause this?

I am using MySQL, InnoDB. Using phpMyAdmin I get the exact same results, so no typo's in column names...

I find it very surprising that the query is working at all in either case. types and type are both MySQL reserved words and shouldn't really have worked without backtick escaping. — apokryfos
– apokryfos, Commented Apr 7, 2016 at 8:15
@apokryfos that is not correct. Niether of them is Reserved and niether of them requires backticks — Hanky Panky
– Hanky Panky, Commented Apr 7, 2016 at 8:18
@apokryfos No, it shows they are key words, and not reserved. — Jonnix
– Jonnix, Commented Apr 7, 2016 at 8:19
@apokryfos do you see an (R) infront of either of those terms? sqlfiddle.com/#!9/e48d6/1 — Hanky Panky
– Hanky Panky, Commented Apr 7, 2016 at 8:19

Community · Accepted Answer · 2017-05-23 12:09:28Z

3

I've found the problem: to fill the table I am reading a textfile per line. The newline character was the problem, it is invisible in phpMyAdmin's browse table view, but I saw it when editing a single row.

The following query fixed my problem:

UPDATE Types SET Type = REPLACE(REPLACE(Type, '\r', ''), '\n', '');

Found in How to remove new line characters from data rows in mysql?

Thanks everyone for your comments.

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Apr 7, 2016 at 8:47

Inigo

3513 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rafaelcpalmeida Over a year ago

I think that's better if you mark it as solved, so the next guy that comes here with the same problem can also be helped :-)

Inigo Over a year ago

Can't do that before 2 days are over, wanted to do it right away... Will do it asap.

Venkat Over a year ago

Very Thanks, It works like charm. I have been struggling for last 2 weeks this issue. Thank you very much. +1

Danyal Sandeelo · Accepted Answer · 2016-04-07 08:19:26Z

0

I am sure that's due to trim

Try this

 SELECT * FROM Types WHERE  TRIM(Type) = 'S 1000 RR'

answered Apr 7, 2016 at 8:19

Danyal Sandeelo

12.4k10 gold badges57 silver badges90 bronze badges

1 Comment

Inigo Over a year ago

Unfortunately, no results

Ria C · Accepted Answer · 2016-04-07 08:56:24Z

0

Its not like that. Your table name and column names are all good and the query is also giving correct o/p: go CREATE TABLE Types ( ID int, Name varchar(3), Type varchar(9));

go
INSERT INTO Types (ID, Name, Type) VALUES (1, 'BMW', 'S 1000 RR')

SELECT * FROM Types WHERE Type = 'S 1000 RR'

answered Apr 7, 2016 at 8:56

Ria C

771 silver badge9 bronze badges

2 Comments

Inigo Over a year ago

It was a newline character at the end that screwed it up. See my answer for details.

Ria C Over a year ago

okok..i was tryin in ssms actually

Collectives™ on Stack Overflow

MySQL simple query returns zero rows [duplicate]

3 Answers 3

3 Comments

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Linked

Related