v2BAwSin

1select * from mytable order by column asc — sort table
2	
31
410
5100
611
712
813
9	
101
1110
1211
1312
1413
15100
16	
17order by cast(column as unsigned) asc
18	
19SELECT * FROM mytable ORDER BY column+0;
201
2110
2211
2312
2413
25100
26	
27SELECT * FROM mytable ORDER BY column+0, column; #this will sort the column by VARCHAR first and then sort it by INT
28
29abc
30xyz
311
3210
3311
3412
3513
36100
37	
38order by (0 + column) asc
39	
40> select * from mytable order by column+0 asc;
41 column
42 1 
43 10
44 11
45 12
46 13 
47 100
48	
49Added a full code script here , but need to sort 1001 and 1002 before - as well.
50We have total 5 solution , means 5 different queries as solution with full script.
51
52
53=============================================================
54    SET NAMES utf8;
55    SET foreign_key_checks = 0;
56    SET time_zone = 'SYSTEM';
57    SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
58
59    DROP TABLE IF EXISTS `varchar_sort`;
60    CREATE TABLE `varchar_sort` (
61      `user_id` int(11) NOT NULL AUTO_INCREMENT,
62      `actual_user_id` varchar(200) DEFAULT NULL,
63      PRIMARY KEY (`user_id`)
64    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
65
66    INSERT INTO `varchar_sort` (`user_id`, `actual_user_id`) VALUES
67    (1, '1001-4'),
68    (2, '1001-1'),
69    (3, '1001-111'),
70    (4, '1002-1'),
71    (5, '1001-66'),
72    (6, '1001-100'),
73    (7, '1001-110');
74
75
76    SELECT user_id,actual_user_id,CONVERT(SUBSTRING_INDEX(actual_user_id,'-',-1),UNSIGNED INTEGER) AS num
77    FROM varchar_sort
78    ORDER BY num;
79
80
81    SELECT user_id,actual_user_id
82    FROM varchar_sort
83    ORDER BY CONVERT(SUBSTRING(actual_user_id, 6), SIGNED INTEGER);
84
85
86    SELECT user_id,actual_user_id
87    FROM varchar_sort
88    ORDER BY CONVERT(SUBSTRING(actual_user_id, LOCATE('-', actual_user_id) + 1), SIGNED INTEGER);
89
90
91    SELECT *, CAST(SUBSTRING_INDEX(actual_user_id, '-', -1) AS UNSIGNED) as num FROM varchar_sort ORDER BY num;
92	
93**Need to sort 1001 and 1002 as well.**