Bad Data Import, Invisible Characters, Can't Select Data Specifically

[colinkites2000]

Hello,

I've been trying to import data into a simple table into PHPmyadmin that will dictate the location of my products. There are 2 columns; product_id and location.

When I try to select the data based on the location, it does not find anything when I use;

WHERE location = 'index2'

However when I use

WHERE location LIKE "%index2%"

the data is found.

I checked the data and there are no visible spaces before and after. When I delete the location record by clicking NULL in PHPmyadmin and then re-type the exact same data, it will select. Does anyone have any idea why this might be happening and / or how I can fix it?

Best,
C

[Fumigator]

There must be some invisible byte getting imported, either some spaces, a tab, or newline, or carraige return, or something...

At any rate, if you are unable to resolve the import issue, you should be able to clean up the data using trim().


UPDATE table1
SET col1 = trim(col1)

[colinkites2000]

Thanks Fumigator....

I tried this where "locations" is the name of my table and "Location" is the name of my column. However, it told me that 0 rows were affected... any idea what I may have done wrong? Or maybe it could not find anything to trim?

UPDATE locations
SET Location = trim(Location)

[Fumigator]

Hmm... MySQL won't update a row if nothing changes, so 0 rows affected means there was nothing to trim, like you said.

How are you importing the data?

[colinkites2000]

I use PHPmyadmin (not sure how to do it any other way) and use the following settings;

Character set of the file: utf8

Format of Imported file:

CSV using LOAD DATA (Replace table data with file, use LOCAL keyword)
Fields terminated by ;
Fields enclosed by "
Fields escaped by \
Lines terminated by auto

Could it be something with the character set ?


Hmm... MySQL won't update a row if nothing changes, so 0 rows affected means there was nothing to trim, like you said.

How are you importing the data?

[colinkites2000]

I found that changing the export setting in my spreadsheet program to use a different carriage return/line break solved this problem. Thanks for you help. C