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 |