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:

If I run either of the following queries:

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

My result is:

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:

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

worked fine for me: http://sqlfiddle.com/#!2/a15ec4/1/3 . Your table isn't actually called table is it? makes me think you have a data problem. sure there's not a return or space in the data somewhere? — xQbert, Dec 01 '14 at 21:43
@xQbert no, just an example name. It's a really bizarre issue; I'm not sure what could be the cause of this one. Is it possible that the data is corrupted somehow? I'm just guessing at this point. — Daniel, Dec 01 '14 at 21:47
you could look up the ascii value for all data and see if you get the values for 0-9... if not you know you have a data integrity issue or just do a length and if > 1 you know you have a data problem. — xQbert, Dec 01 '14 at 21:48
@xQbert great suggestion - is there a quick way of accomplishing that via SQL that you know of? If not I'll research it further. — Daniel, Dec 01 '14 at 21:50
`select length(data) as mLEN from table having mlen>1` for length — xQbert, Dec 01 '14 at 21:53
What does `SELECT data, LENGTH(data), LENGTH(TRIM(data)), ASCII(data) FROM table` return? Are you using multibyte character encoding? — Bacon Bits, Dec 01 '14 at 21:55
`select ascii(data) from foo where ascii(data) not between 48 and 57` for ascii characters... not 0-9 — xQbert, Dec 01 '14 at 21:57
@BaconBits I ran those queries you recommended and got the above results (added to question). Thanks for the suggestion! If you put your suggestion in an answer, I'll vote up/accept to give you some cred. — Daniel, Dec 02 '14 at 18:01
@xQbert I ran your ASCII query and found my issue. Thanks for all of your help! If you put your thoughts in an answer, I'll vote up/accept to give you some credit for your work. — Daniel, Dec 02 '14 at 18:02

score 1 · Accepted Answer · answered Dec 02 '14 at 18:16

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?

answered Dec 02 '14 at 18:16

Bacon Bits

30,782
5
59
66

score 1 · Answer 2 · answered Dec 02 '14 at 18:28

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.

Eduard Uta · Answer 3 · 2014-12-01T21:42:27.280

0

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

edited Dec 01 '14 at 21:42

answered Dec 01 '14 at 21:36

Eduard Uta

2,477
5
26
36

Thanks for the suggestion; unfortunately, both of these queries returned `0` for each data point. This may be a really bizarre issue. – Daniel Dec 01 '14 at 21:45
@Daniel, do the values also contain some unwanted space? Can you copy the values and paste them in a notepad and check for extra white spaces? That might be the issue. – Eduard Uta Dec 01 '14 at 21:49
That's a great suggestion @Eduard Uta, I'll give it a shot. – Daniel Dec 01 '14 at 21:51
1

Spaces can't be the problem since they don't affect `CAST` and `CONVERT` - See http://sqlfiddle.com/#!8/13d35/2 – Fabien TheSolution Dec 01 '14 at 22:08
what about a hard return? – xQbert Dec 01 '14 at 22:09

Joe Swindell · Answer 4 · 2014-12-01T21:49:24.160

0

Try these syntax

SELECT CONVERT(data, UNSIGNED INTEGER) FROM table

or

SELECT CAST(data AS UNSIGNED) FROM table

edited Dec 01 '14 at 21:49

answered Dec 01 '14 at 21:37

Joe Swindell

684
1
7
18

Thanks a lot for the suggestions, although: `SELECT CONVERT(data, UNSIGNED INTEGER) FROM table` gives the same result of `0`, and `SELECT CONVERT(data AS UNSIGNED) FROM table` yields a syntax error. I think the `AS` syntax only works with `CAST()`. – Daniel Dec 01 '14 at 21:42
Yes sorry the 2nd should have been cast – Joe Swindell Dec 01 '14 at 21:50

MySQL VARCHAR Type won't CONVERT to Integer

4 Answers4