Order by numbers followed by alphabet and then null values

Question

I have below column in my database:

abc
1
2
null
100
6

I want to sort and show these values as per below sequence:

1
2
6
100
abc
null

Anyone please help me to solve it. Consider below query:

select * from test_table where status=1 order by test_column DESC

OR

 select * from test_table where status=1 order by if((test_column = '' OR test_column IS NULL),'999999',test_column) DESC

Answer 1

select
*
from
t
order by
case 
when col regexp '^[0-9]' then 1
when col regexp '^[a-zA-Z]' then 2
when col = '' or col is null then 3
end
, col * 1 /*this converts to number, so that 100 is not sorted before 2*/
, col /*finally sort strings correctly*/

Answer 2

With schema and data like this:

create table t1( data text);

insert into t1 (data) values ('100');
insert into t1 (data) values ('6');
insert into t1 (data) values ('1');
insert into t1 (data) values ('2');
insert into t1 (data) values ('abc');
insert into t1 (data) values (null);

you can run this query:

select data, 
case when length(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(data,'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1',''),'0','')) = 0 then 0 else case when data is null then 99 else 50 end end sort_,
case when length(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(data,'9',''),'8',''),'7',''),'6',''),'5',''),'4',''),'3',''),'2',''),'1',''),'0','')) = 0 then CAST(data AS SIGNED) else 0 end int_
from t1 order by 2 asc, 3 asc;

with result:

+------+-------+------+
| data | sort_ | int_ |
+------+-------+------+
| 1    |     0 |    1 |
| 2    |     0 |    2 |
| 6    |     0 |    6 |
| 100  |     0 |  100 |
| abc  |    50 |    0 |
| NULL |    99 |    0 |
+------+-------+------+

Answer 3

I have found the solution as below : Datatype of column is MEDIUMTEXT so i have converted string to numbers and used below solution :

Order by (test_column+0 != 'zzzzzz' IS NOT TRUE) ,test_column+0 ASC

OR

Order by (test_column+0 != 'zzzzzz' IS NOT TRUE) ,cast(test_column as unsigned) ASC

Answer 4

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.01 sec)

mysql> create table test (txt varchar(8));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into test values ('abc'),(1),(2),(null),(100),(6),(0),('def'),('cde'),(0.4),(0.6);
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from test order by cast(concat('0', txt)+0 as char)=txt DESC, 0+txt, txt;
+------+
| txt  |
+------+
| 0    |
| 0.4  |
| 0.6  |
| 1    |
| 2    |
| 6    |
| 100  |
| abc  |
| cde  |
| def  |
| NULL |
+------+
11 rows in set, 3 warnings (0.00 sec)

Fails on negative numbers.