MySQL VARCHAR Type won't CONVERT to Integer

Question

I have a column of data of type VARCHAR, that I want to CONVERT or CAST to an integer (my end goal is for all of my data points to be integers). However, all the queries I attempt return values of 0.

My data looks like this:

1
2
3
4
5

If I run either of the following queries:

SELECT CONVERT(data, BINARY) FROM table
SELECT CONVERT(data, CHAR) FROM table

My result is:

1
2
3
4
5

No surprises there. However, if I run either of these queries:

SELECT CONVERT(data, UNSIGNED) FROM table
SELECT CONVERT(data, SIGNED) FROM table

My result is:

0
0
0
0
0

I've searched SO and Google all over for an answer to this problem, with no luck, so I thought I would try the pros here.

EDIT/UPDATE

I ran some additional queries on the suggestions from the comments, and here are the results:

data LENGTH(data) LENGTH(TRIM(data)) ASCII(data)
1    3            3                  0
2    3            3                  0
3    3            3                  0
4    3            3                  0
5    3            3                  0

It appears that I have an issue with the data itself. For anyone coming across this post: my solution at this point is to TRIM the excess from the data points and then CONVERT to UNSIGNED. Thanks for all of the help!

FURTHER EDIT/UPDATE

After a little research, turns out there were hidden NULL bytes in my data. The answer to this question helped out: How can I remove padded NULL bytes using SELECT in MySQL

Answer 1

What does SELECT data, LENGTH(data), LENGTH(TRIM(data)), ASCII(data) FROM table return? It's possible your numeric strings aren't just numeric strings.

Alternately, are you using multi-byte character encoding?

Answer 2

I believe this is the correct form:

SELECT CAST(data AS UNSIGNED) FROM test;
SELECT CAST(data AS SIGNED) FROM test;

Tested here: http://sqlfiddle.com/#!8/8c481/1

Answer 3

Try these syntax

SELECT CONVERT(data, UNSIGNED INTEGER) FROM table

or

SELECT CAST(data AS UNSIGNED) FROM table

Answer 4

I believe the query you have is fine; as it worked for me: sqlfiddle.com/#!2/a15ec4/1/3.

Makes me think you have a data problem. Are you sure there's not a return or space in the data somewhere?

you can check the data by trying to do a length or a ascii on the data to see if you have more than expected:

select ascii(data) from foo where ascii(data) not between 48 and 57 or

select length(data) as mLEN from table having mlen>1 for length.