Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytesting (
val VARCHAR2(50) NOT NULL);
INSERT ALL
  INTO mytesting (val) VALUES ('3')
  INTO mytesting (val) VALUES ('8')
  INTO mytesting (val) VALUES ('56')
  INTO mytesting (val) VALUES ('70')
  INTO mytesting (val) VALUES ('90')
  INTO mytesting (val) VALUES ('KA')
  INTO mytesting (val) VALUES ('AK')
  INTO mytesting (val) VALUES ('CN')
  INTO mytesting (val) VALUES ('PP')
  INTO mytesting (val) VALUES ('PQ')
  INTO mytesting (val) VALUES ('W3')
  INTO mytesting (val) VALUES ('0.5')
  INTO mytesting (val) VALUES ('0.6')
  INTO mytesting (val) VALUES ('0.8')
  INTO mytesting (val) VALUES ('040')
  INTO mytesting (val) VALUES ('070')
  INTO mytesting (val) VALUES ('1.2')
  INTO mytesting (val) VALUES ('1.5')
  INTO mytesting (val) VALUES ('1.6')
  INTO mytesting (val) VALUES ('100')
  INTO mytesting (val) VALUES ('150')
  INTO mytesting (val) VALUES ('187')
  INTO mytesting (val) VALUES ('2.8')
  INTO mytesting (val) VALUES ('250')
  INTO mytesting (val) VALUES ('3.0')
  INTO mytesting (val) VALUES ('6.3')
  INTO mytesting (val) VALUES ('800')
  INTO mytesting (val) VALUES ('8mm')
SELECT * FROM dual;
28 rows affected

SELECT val,
       CASE WHEN REGEXP_LIKE(val,'^[0-9]')
                   THEN CAST(REGEXP_REPLACE(val,'[a-zA-Z]','')+0 AS DEC(5,2)) 
              WHEN REGEXP_REPLACE(val,'[^a-zA-Z]','') IS NULL
                   THEN CAST(val AS DEC(5,2)) 
              ELSE 9000*9000 END AS val_check
       FROM mytesting 
ORDER BY CASE WHEN REGEXP_LIKE(val,'^[0-9]')
                   THEN CAST(REGEXP_REPLACE(val,'[a-zA-Z]','')+0 AS DEC(5,2)) 
              WHEN REGEXP_REPLACE(val,'[^a-zA-Z]','') IS NULL
                   THEN CAST(val AS DEC(5,2)) 
              ELSE 9000*9000 END,
              val


VAL
VAL_CHECK


0.5
.5

0.6
.6

0.8
.8

1.2
1.2

1.5
1.5

1.6
1.6

2.8
2.8

3
3

3.0
3

6.3
6.3

8
8

8mm
8

040
40

56
56

070
70

70
70

90
90

100
100

150
150

187
187

250
250

800
800

AK
81000000

CN
81000000

KA
81000000

PP
81000000

PQ
81000000

W3
81000000

VAL	VAL_CHECK
0.5	.5
0.6	.6
0.8	.8
1.2	1.2
1.5	1.5
1.6	1.6
2.8	2.8
3	3
3.0	3
6.3	6.3
8	8
8mm	8
040	40
56	56
070	70
70	70
90	90
100	100
150	150
187	187
250	250
800	800
AK	81000000
CN	81000000
KA	81000000
PP	81000000
PQ	81000000
W3	81000000