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 |