Records visible but not accessible in MySQL - why!?!?!

Question

This is a weird issue. I'm accessing my online database using premiumsofts Navicat for mysql. Some of the records are behaving very strange - let me give an example. I have the following table columns id, name, address, abbreviation, contact. Now when I run a sql query for lets say any entry that has the abbreviation 'ab' it returns zero however such an entry already exists in the database.

Whats even weirder is that when I view the table in navicat - I notice the field of abbreviation is empty for that tuple which has the required value but when I hover over it or highlight it - I can see the value. Its there but its inaccessible and likewise this is a problem with many other tuples in the table.

What could the problem be here - I even tried to delete and recreate the table by executing a dump file but no good came out of that. Help please :(

Answer 1

Check that there aren't any invisible characters at the beginning of the string (like a carriage return or something).

Answer 2

As you can see from the following example, there can be some junk extra character like A0 and should be removed using update.

mysql> select add_code, unhex(replace(hex(add_code), 'A0', '')) from old_new limit 1\G

*********************** 1. row ***********************

add_code: 000242�

unhex(replace(hex(add_code), 'A0', '')): 000242

1 row in set (1.32 sec)

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_unhex