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