CODEWITHSUNDEEP
mysql
IssamTP
IssamTP

Reputation: 2440

MySQL varchar column filled but not visible


I'm having a problem with a column ( VARCHAR(513) NOT NULL ) on a MySQL table.
During a procedure of import from a CSV file, a bunch of rows got filled with some weird stuff coming from I don't know where.
This stuff is not visible from Workbench, but if I query the DBMS with:

SELECT * FROM MyTable;
I got:
ID | Drive | Directory        | URI          | Type ||
1  | Z:    | \Users\Data\     | \server\dati | 1    || // <-correct row
...
32 | NULL  | \Users\OtherDir\ |              | 0    ||
While row 1 is correct, row 32 shows a URI filled with something. Now, if I query dbms with:
SELECT length(URI) FROM MyTable WHERE ID = 32;
I got 32. While, doing:
SELECT URI FROM MyTable WhERE ID = 32;
inside a MFC application, gets a string with length 0.
Inside this program I have a tool for handling this table but this cannot work because I cannot build up queries about rows with bugged URI: how can I fix this? Where this problem comes from? If you need more information please ask. Thanks.

Upvotes: 1

Views: 355

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Looks like you have white spaces in the data and which is causing the issue and when you import data from CSV its most often happen.

So to fix it you may need to run the following update statement

update MyTable set URI = trim(URI);

The above will remove the white spaces from the column.

Also while importing data from CSV its better to use the TRIM() for the values before inserting into the database and this will avoid this kind of issues.

Upvotes: 1

Related Questions