MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytest (
  vals VARCHAR(255));

INSERT INTO mytest VALUES
('0057'), ('0058'), ('10'), ('11'), ('5'), ('6'), ('A5'), ('A6');

CREATE TABLE mytest2 (
  vals VARCHAR(255));

INSERT INTO mytest2 VALUES
('1'), ('2'), ('0057'), ('0058'), ('10'), ('12'), ('A1'), ('A2');

Records: 8  Duplicates: 0  Warnings: 0



Records: 8  Duplicates: 0  Warnings: 0


SELECT *,
        CASE WHEN vals LIKE '0%' THEN 1
        WHEN vals REGEXP '[a-zA-Z]' THEN 999
        ELSE vals    
   END AS sorter
  FROM mytest
ORDER BY CAST(sorter AS SIGNED), vals;

SELECT *,
        CASE WHEN vals LIKE '0%' THEN 1
        WHEN vals REGEXP '[a-zA-Z]' THEN 999
        ELSE vals    
   END AS sorter
  FROM mytest2
ORDER BY CAST(sorter AS SIGNED), vals;


vals
sorter


0057
1

0058
1

5
5

6
6

10
10

11
11

A5
999

A6
999




vals
sorter


0057
1

0058
1

1
1

2
2

10
10

12
12

A1
999

A2
999


SELECT *
  FROM mytest
ORDER BY CAST(CASE WHEN vals LIKE '0%' THEN 1
        WHEN vals REGEXP '[a-zA-Z]' THEN 999
        ELSE vals    
   END AS SIGNED), vals;


vals


0057

0058

5

6

10

11

A5

A6