CREATE TABLE IF NOT EXISTS `varchar_order_by` ( `text` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (`text`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `varchar_order_by` (`text`) VALUES ('1'), ('10'), ('100'), ('101'), ('11'), ('12'), ('15'), ('1abc'), ('2'), ('23abcd'), ('2abcd'), ('3'), ('4'), ('8'), ('80'), ('85'), ('99'), ('abcd'), ('bcd'), ('dfgh');
Mysql Query
select text regexp '[a-zA-Z\s]' , text + 0 , text regexp '[a-zA-Z\s]' AND text + 0, text from varchar_order_by order by text regexp '[a-zA-Z\s]' asc, text regexp '[a-zA-Z\s]' AND text + 0 desc, text + 0 asc, text asc