String to Int conversion fails

[Bitweiser]

Hi aces,

I'm fairly new to SQL and I have the following question.  In one of my tables there is a colomn set as varchar.  Only it contains only numeric values (with one exeption).  When i'm trying to order by this colomn my result is like this: 1, 100, 1000, 2, 200 and not 1, 2, 100, 200, 1000.  This is logic because the values are char and not int.  So I've tried to convert them into an number like so:

 

SELECT T1.Person_ID, T1.Person_Name,
             T2.Organisation_ID
FROM tb_Persons AS T1
JOIN  tb_organisations AS T2
   ON   T1.Person_ID = T2.Organisation_ID
WHERE T2.Organisation_ID != 'MAIN'                    // avoid the only alfanumeric exception in the col.
ORDER BY CAST(T2.Organisation_ID AS INT);

This is the result:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT)' at line 7

 

In the same way I've tried "ORDER BY CONVERT(INT, T2.Organisation_ID)" with the same errormsg.

 

Is this due to the one real alfanumeric value in the table because imho the order is executed on de resultset (thus without the 'main')?

 

Help is greatly appreciated...

 

[Bitweiser]

Ok, I figured it out, don't ask me how or why but it works...

ORDER BY (CAST(T2.Organisation_ID AS unsigned Integer);

This does the job.  The "unsigned" makes everything work...

[mikosiko]

Ok, I figured it out, don't ask me how or why but it works...

ORDER BY (CAST(T2.Organisation_ID AS unsigned Integer);

This does the job.  The "unsigned" makes everything work...

 

this is the reason why it works and your previous intent don't.... read the allowed "types" below CAST in the function CONVERT

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast