Oracle 21c

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

CREATE TABLE data_table (
    parameter_type VARCHAR2(10) NOT NULL
);

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

SELECT
  parameter_type,
  REGEXP_INSTR(parameter_type, '^[0-9]') AS instr,
  REGEXP_REPLACE(parameter_type, '[^0-9\.]', '') AS replace,
  CASE WHEN REGEXP_INSTR(parameter_type, '^[0-9]') > 0 THEN TO_NUMBER(REGEXP_REPLACE(parameter_type, '[^0-9\.]', '')) END AS sort
FROM data_table

ORDER BY
  CASE WHEN REGEXP_INSTR(parameter_type, '^[0-9]') > 0 THEN TO_NUMBER(REGEXP_REPLACE(parameter_type, '[^0-9\.]', '')) END
  , parameter_type


PARAMETER_TYPE
INSTR
REPLACE
SORT


0.5
1
0.5
.5

0.6
1
0.6
.6

0.8
1
0.8
.8

1.2
1
1.2
1.2

1.5
1
1.5
1.5

1.6
1
1.6
1.6

2.8
1
2.8
2.8

3
1
3
3

3.0
1
3.0
3

6.3
1
6.3
6.3

8
1
8
8

8mm
1
8
8

040
1
040
40

56
1
56
56

070
1
070
70

70
1
70
70

90
1
90
90

100
1
100
100

150
1
150
150

187
1
187
187

250
1
250
250

800
1
800
800

AK
0
null
null

CN
0
null
null

KA
0
null
null

PP
0
null
null

PQ
0
null
null

W3
0
3
null

PARAMETER_TYPE	INSTR	REPLACE	SORT
0.5	1	0.5	.5
0.6	1	0.6	.6
0.8	1	0.8	.8
1.2	1	1.2	1.2
1.5	1	1.5	1.5
1.6	1	1.6	1.6
2.8	1	2.8	2.8
3	1	3	3
3.0	1	3.0	3
6.3	1	6.3	6.3
8	1	8	8
8mm	1	8	8
040	1	040	40
56	1	56	56
070	1	070	70
70	1	70	70
90	1	90	90
100	1	100	100
150	1	150	150
187	1	187	187
250	1	250	250
800	1	800	800
AK	0	null	null
CN	0	null	null
KA	0	null	null
PP	0	null	null
PQ	0	null	null
W3	0	3	null