SQL order string as number

If possible you should change the data type of the column to a number if you only store numbers anyway.

If you can't do that then cast your column value to an integer explicitly with

select col from yourtable
order by cast(col as unsigned)

or implicitly for instance with a mathematical operation which forces a conversion to number

select col from yourtable
order by col + 0

BTW MySQL converts strings from left to right. Examples:

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123 
'$123'        |  0   /* the left side of the string does not start with a number */

Another way, without using a single cast.

(For people who use JPA 2.0, where no casting is allowed)

select col from yourtable
order by length(col),col

EDIT: only works for positive integers

Another and simple way

ORDER BY ABS(column_name)

The column I'm sorting with has any combination of alpha and numeric, so I used the suggestions in this post as a starting point and came up with this.

DECLARE @tmp TABLE (ID VARCHAR(50));
INSERT INTO @tmp VALUES ('XYZ300');
INSERT INTO @tmp VALUES ('XYZ1002');
INSERT INTO @tmp VALUES ('106');
INSERT INTO @tmp VALUES ('206');
INSERT INTO @tmp VALUES ('1002');
INSERT INTO @tmp VALUES ('J206');
INSERT INTO @tmp VALUES ('J1002');

SELECT ID, (CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END) IsNum
FROM @tmp
ORDER BY IsNum, LEN(ID), ID;

Results

ID
------------------------
106
206
1002
J206
J1002
XYZ300
XYZ1002

Hope this helps

This works for me.

select * from tablename
order by cast(columnname as int) asc