3

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...

Inigo
  • 341
  • 3
  • 12
  • 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 Apr 07 '16 at 08:15
  • 3
    @apokryfos that is not correct. Niether of them is Reserved and niether of them requires backticks – Hanky Panky Apr 07 '16 at 08:18
  • @HankyPanky [this page](https://dev.mysql.com/doc/refman/5.7/en/keywords.html) seems to claim otherwise – apokryfos Apr 07 '16 at 08:19
  • @apokryfos No, it shows they are key words, and not reserved. – Jonnix Apr 07 '16 at 08:19
  • @apokryfos do you see an `(R)` infront of either of those terms? http://www.sqlfiddle.com/#!9/e48d6/1 – Hanky Panky Apr 07 '16 at 08:19
  • Check in database whtr S 1000 RR has spaces on either sides? – Mangesh Sathe Apr 07 '16 at 08:20
  • Hmm @apokryfos, was too eager, this did not fix my problem. There are no spaces on either side. TRIM(Type) also did not work. I am guessing there is a newline in the value somewhere, as I read the Types line per line from a text file... But this shouldn't show up in the varchar value. – Inigo Apr 07 '16 at 08:38
  • Would `WHERE Type LIKE 'S 1000 RR'` fare any better? – apokryfos Apr 07 '16 at 08:42
  • I've found the problem: it was indeed a newline character that doesn't show up in the browse view of phpMyAdmin. When editing the row, I could see that there was an 'enter', a newline in the value. Removing this newline allowed me to search the value. – Inigo Apr 07 '16 at 08:43

3 Answers3

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.

Community
  • 1
  • 1
Inigo
  • 341
  • 3
  • 12
  • 1
    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 :-) – rafaelcpalmeida Apr 07 '16 at 08:50
  • Can't do that before 2 days are over, wanted to do it right away... Will do it asap. – Inigo Apr 07 '16 at 09:03
  • 1
    Very Thanks, It works like charm. I have been struggling for last 2 weeks this issue. Thank you very much. +1 – Venkat Mar 14 '17 at 13:43
0

I am sure that's due to trim

Try this

 SELECT * FROM Types WHERE  TRIM(Type) = 'S 1000 RR'
Danyal Sandeelo
  • 12,196
  • 10
  • 47
  • 78
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'
Ria C
  • 77
  • 8